How to Implement Relative Month to Date Filter in Power BI

Introduction:

During my experience with Power BI, I’ve always been amazed by what it can do. However, a recent project brought me face to face with an interesting challenge: How to effectively use the “Month to Date Filter in Power BI” to capture data precisely from the start of the current month up to the present day? Power Bi does have some relative date filter options, but it did not serve my needs.

The Problem: Relative Month To Date Filter Power BI not available:

While exploring, I noticed Power BI’s relative date filtering options, such as “is this month,” which encompasses an entire month. This feature, though useful, fell short in my specific scenario.

Picture this: It’s mid-November 2023, and you’re comparing your current sales from November 1st to the 15th with the previous year’s full-month sales. The standard “Month to date filter in Power BI” shows your sales at $500, against last year’s $1000 for the whole of November. At first glance, it seems like a 50% drop in sales year-over-year. However, this isn’t a fair comparison – half a month’s data against a full month.

The real question then becomes: How can we tweak the “relative date filter in Power BI” to allow for more accurate comparisons?

Definition Month to Date (MTD): What is MTD?

MTD stands for “Month to Date.” It represents the time-period that starts from the beginning of the current month up until today, excluding today’s date to account for data completeness. MTD is a valuable tool for gaining insights into specific activities, campaign results, or other data within this precise time frame.

For example, if today’s date is the 8th of December, MTD would encompass activities and data from the 1st of December to the 7th of December, inclusive.

Solution:

Now that we understand what MTD means, let’s delve into how to implement it effectively in Power BI.

1. You Must Have a Date Table:

To make MTD calculations in Power BI, you need a date table that contains a range of dates.

2. Create a New Calculated Column:

In your date calendar, create a new calculated column that will help you determine whether a date falls within the MTD range. Be sure to rename the date table to match your report for clarity. You can copy past the code below, you simply need to adjust the table and column name o of your date field!

The “MDT_Check” measure is like a smart filter for dates. It figures out if a date falls within the Month To Date (MTD) range. If today is the 1st day of the month, it looks at the entire previous month’s data. But if it’s not the 1st day of the month, it checks from the start of this month up to yesterday. It’s a handy tool for analyzing data within a specific time frame, like how your performance is this month compared to the previous one.

MDT_Check = -- Checks if date falls within Month To Date criteria

-- Define the day just before today
VAR DayBeforeToday = TODAY() - 1

-- Check if the current day is the 1st day of the month
VAR IsTodayFirstDayOfMonth = IF(DAY(TODAY()) = 1, TRUE(), FALSE())

-- Get the last day of the previous month
VAR PreviousMonthLastDate = EOMONTH(DayBeforeToday, -1)

-- Determine the first day of the previous month
VAR PreviousMonthStartDate = DATE(YEAR(PreviousMonthLastDate), MONTH(PreviousMonthLastDate), 1)

RETURN
-- If today is the first day of the month, include the entire previous month
IF(
    IsTodayFirstDayOfMonth,
    dTime[date] >= PreviousMonthStartDate && dTime[date] <= PreviousMonthLastDate,
    -- If it's not the first day, include dates from the start of the current month up to yesterday
    dTime[date] >= DATE(YEAR(DayBeforeToday), MONTH(DayBeforeToday), 1) && dTime[date] <= DayBeforeToday
)

3. True or False Outcome.

The calculated column will yield either “True” or “False” for each date, indicating whether it falls within the MTD period.
Example: At the time of writing this article it is the 08.12.2023 which means the outcome of the calculated column will show “TRUE” for all dates ranging and including from 01.12.2023 until 07.12.2023.

4. Filter the Visual Based on True/False:

Finally, you can use this calculated column to filter your visualizations. By filtering based on “True,” you’ll isolate the data that falls within the MTD range, providing you with the precise insights you need.

Conclusion: Relative Month To Date Filter Power BI

In conclusion, understanding and using the “MDT_Check” measure in Power BI opens doors to precise data analysis. With MTD filters, you can compare data within specific timeframes, making informed decisions in areas like campaign performance and sales tracking.

Harness the power of MTD filters to enhance your Power BI reporting and gain deeper insights into your data.