Search code examples
powerbidaxpowerbi-desktopdaxstudio

How to Create a Slicer that uses dates and that only contains YTD Months dynamically by current date in PowerBI


Trying to create a slicer that when clicked only the months YTD, specifically up the the last month of current.

Such as slicer box clicked: (Say current month is August) then Months "Jan","Feb",..."July" would only display to click.

I've tried several things, my last attempt was to create a Dax table with dates and try to do a switch:

Dates = 
var CurrentMonthInt = month(TODAY())
var monthis8 = "Jan" + "Feb" + "March" + "April" + "May" + "June" + "July"
VAR BaseTable = 
CALENDAR(
    DATE( YEAR ( MIN(Reporting[InvoiceDate])),01,01),
    DATE( YEAR ( MAX(Reporting[InvoiceDate])),12,31)
)
RETURN
 ADDCOLUMNS(
     BaseTable,
     "Year", YEAR([Date]),
     "Month", FORMAT([Date],"mm"),
     "Year Month", FORMAT([Date],"YYYY MM"),
     "Month YTD",   SWITCH(TRUE(), 
    CurrentMonthInt = 1, "Jan",
    CurrentMonthInt = 8,  monthis8,
    "testing"
    )
 )

this returns a variant data type and will not work. I am thinking this is trying to add them all up on the same row. 

Solution

  • Hello Please test this and let me know if It solves your problem.

    Dates =
    VAR CurrentMonthInt =
        MONTH ( TODAY () )
    VAR BaseTable =
        CALENDAR (
            DATE ( YEAR ( MIN ( Reporting[InvoiceDate] ) ), 01, 01 ),
            DATE ( YEAR ( MAX ( Reporting[InvoiceDate] ) ), 12, 31 )
        )
    RETURN
        FILTER (
            ADDCOLUMNS (
                BaseTable,
                "Year", YEAR ( [Date] ),
                "Month", MONTH ( [Date] ),
                "Year Month", FORMAT ( [Date], "YYYY MM" )
            ),
            [Month] <= CurrentMonthInt - 1
        )
    

    Note: I haven't tested it.