Drop down filter for YTD, Current Month and Current Week

[Heather]

@musa.fasih

CALCULATE( [Total Sales], FILTER(‘Dates’, ‘Dates’[Year] = YEAR(TODAY()) && ‘Dates’[Month] = MONTH(TODAY())))

This type of a problem is VERY easily solved with @Melissa’s Magnificent Date Table (and if you are not using it, I highly recommend)

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

with that table in place, we only need to filter on the month offset (0 = current month)

Current Mth (month offset) = 
CALCULATE( [Total Sales],
    FILTER( Dates, Dates[MonthOffset] = 0 ))

But, if that is not possible (although again, I highly recommend the switch), then you can try something like this:

Current Mth (Filtered Calendar) = 
VAR _StartMonth = DATE( YEAR(TODAY()), MONTH(TODAY()), 1)
VAR _Current = TODAY()
RETURN
    CALCULATE( [Total Sales], 
        FILTER( Dates, 
            Dates[Date] >= _StartMonth && Dates[Date] <= _Current ))

Note that these methods both specifically force to the CURRENT MONTH - your user won’t have the option to select a different month (for that you would need to identify the selected month and use that in the variables above).

updated copy is attached
Filter by YTD or MTD with a slicer.pbix (389.1 KB)