Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Morning;
I'm trying to use the Sunburst Visual to show the last week and the week before that's sales for the section from which they were sold as well as the day of the week they were sold in.
When I use the column value it appears; but shows all of the data.
When I use my past week measure as the Value; it doesn't show anything and I'm not sure why?
P1WNetSales = VAR CurrentWeekNum = MAX ( 'ExtendedCalendar'[WeekNum] ) - 1 VAR CurrentYear = MAX ( 'ExtendedCalendar'[Year] ) RETURN ( CALCULATE ( SUM ( 'itemdetailsdogfood$'[Net Sales] ), FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear && 'ExtendedCalendar'[WeekNum] = CurrentWeekNum - 0 ) ) )
Do I need to make it a column instead? I've tried copying the code and nothing appears in the column.
Solved! Go to Solution.
Hi @ElliotP,
After looking into your shared pbix file, I found that the variable "CurrentWeekNum" should be defined as below in order to get the max week of current year, or it will return the max week of all years(not only the current year).
VAR CurrentWeekNum = CALCULATE ( MAX ( 'ExtendedCalendar'[WeekNum] ) - 2, FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear ) )
And following is the formula to create the measure. And it can be shown on the Card visual or on the Chart with a Date column.
P2WNetSales = VAR CurrentYear = MAX ( 'ExtendedCalendar'[Year] ) VAR CurrentWeekNum = CALCULATE ( MAX ( 'ExtendedCalendar'[WeekNum] ) - 2, FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear ) ) RETURN ( CALCULATE ( SUM ( 'itemdetailsdogfood$'[Net Sales] ), FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear && 'ExtendedCalendar'[WeekNum] = CurrentWeekNum - 0 ) ) )
Regards
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |