Forum Discussion

Vivdroid_C_4222's avatar
Vivdroid_C_4222
Frequent Visitor
1 year ago
Solved

Help! How do I show MTD dates in Table Visual.

I have a date slicer, and the slicer type is set to "before" to show only the end date. I have a table visual where I have added dates in columns. Now if I select 6 june 2025 in slicer, I want ...
Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    1 year ago

    Hi Vivdroid_C_4222,

    Thank you for the update. Could you please share the sample data related to your thread? This would help me reproduce the scenario and work towards a solution. Please provide the sample data in table format, along with the expected outcome based on your thread.

    Thank you.

Nasif_Azam's avatar
Nasif_Azam
Super User
1 year ago

Hey Vivdroid_C_4222 ,

You can achieve Month-To-Date (MTD) filtering in your Power BI table visual with a "Before" date slicer by creating a calculated column or a DAX measure that dynamically determines whether a date falls within the MTD range. 

 

1. Create a new column for Day and Month

Make sure you have a proper Date table marked as a date table. If not, create one like this:

DateTable = CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31))

Then add the necessary columns:

Year = YEAR(DateTable[Date])
Month = MONTH(DateTable[Date])
Day = DAY(DateTable[Date])

 

2. Create a disconnected Date slicer

You already have a slicer set to "Before". Make sure it's using a separate date table like SlicerDateTable[Date].

 

3. Create a Measure to Flag MTD Dates

Now create a measure that flags rows as TRUE if the date is in the same month and year as the selected date and is less than or equal to the selected date:

IsMTDDate =
VAR SelectedDate = MAX('SlicerDateTable'[Date])
RETURN
    IF(
        'DateTable'[Date] <= SelectedDate &&
        MONTH('DateTable'[Date]) = MONTH(SelectedDate) &&
        YEAR('DateTable'[Date]) = YEAR(SelectedDate),
        1,
        0
    )

 

4. Apply Visual Filter

Go to your table visual and drag DateTable[Date] into it. Then:

  • Drag the IsMTDDate measure to the filter pane of the visual.

  • Set the filter to IsMTDDate = 1.

 

Works Like

  • When you select June 6, 2025, the logic includes all dates from June 1 to June 6, 2025.

  • When you select May 4, 2025, it includes May 1 to May 4, 2025.

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


Best Regards,
Nasif Azam