Hello, I have having a lot of trouble with a date period selection. I’ve just got started on DAX, I must say that I stole this code from another forum ( https://community.powerbi.com/t5/Power-Query/How-to-calculate-MTD-last-7-days-Yesterday-today-date-range/m-p/731354#M24307)
Time Period =
VAR tod =
TODAY()
VAR yd =
YEAR( TODAY() )
VAR md =
MONTH( TODAY())
VAR yest = tod - 1
VAR last7 = tod - 7
VAR sele =
SELECTEDVALUE( Slicer[TYPE] )
VAR todaysales =
CALCULATE(
SUM( Dates[Date] ),
FILTER(Dates,'Dates'[Date] = tod )
)
VAR yestsales =
CALCULATE(
SUM(Dates[Date]),
FILTER(Dates, 'Dates'[Date] <= tod && 'Dates'[Date] > last7 )
)
VAR last7sales =
CALCULATE(
SUM('Dates'[Date]),
FILTER('Dates', 'Dates'[Date] <= tod && 'Dates'[Date] > last7 )
)
VAR mtdsale =
CALCULATE(
SUM('Dates'[Date]),
FILTER(
'Dates',
YEAR('Dates'[Date]) = yd
&& MONTH('Dates'[Date]) = md
&& 'Dates'[Date] <= tod
)
)
RETURN
IF(
ISFILTERED ( Slicer[TYPE] ),
SWITCH(
TRUE(),
sele = "Today", todaysales,
sele = "Yesterday", yestsales,
sele = "Last 7 Days", last7sales,
sele = "MTD", mtdsale
)
)
My Slicer table looks like this (to be honest I do not know if I have done this correctly)
Slicer = {
(1,"Today"),
(2,"Yesterday"),
(3,"Last 7 Days"),
(4,"MTD")
}
If I drag a slicer onto the canvas and put ‘Type’ into the ‘Filters on this visual’, it appears right on the screen, but when I make a selection nothing changes, all the dates come up. I should also mention that the slicer table is not joined to anything, I’m not sure what I’d join it on considering the date table is in the code.
This is the code for the date table I created:
Dates =
VAR BaseCalendar =
CALENDARAUTO(6)
RETURN
GENERATE(
BaseCalendar,
VAR BaseDate = [Date]
VAR YearDate = YEAR( BaseDate )
VAR MonthNumber = MONTH( BaseDate )
RETURN ROW(
"Day", BaseDate,
"Year", YearDate,
"Month Number", MonthNumber,
"Month", FORMAT( BaseDate, "mmmm"),
"Year Month", FORMAT( BaseDate, "mmm yy"),
"Year QTR", FORMAT( BaseDate, "\QTR q")
)
)
It is my first time posting here, I’ve been a member for a few years, but could not get any Power BI work in that time (would you believe!), I’m enjoying it, but this is killing me!
Thank you in advance for any help, happy to provide an additional info as needed. I’m on Australia time.
Regards,
P.