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.
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.