Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need to calculate the previous totals from the past 6 weeks in a bar graph. Below is the code I have and I keep getting an error.
Units PW =
VAR CurrentWeek = SELECTEDVALUE(TB_MDH_DATA_DAILY_MONTHLY[WeekNum])
VAR CurrentYear = SELECTEDVALUE(TB_MDH_DATA_DAILY_MONTHLY[DT].[Year])
VAR MaxWeekNumber = CALCULATE(MAX(TB_MDH_DATA_DAILY_MONTHLY[WeekNum]), ALL(TB_MDH_DATA_DAILY_MONTHLY[DT]))
RETURN
SUMX(
FILTER (ALL(TB_MDH_DATA_DAILY_MONTHLY[DT]),
IF(CurrentWeek = 1,
WEEKNUM = MaxWeekNumber && TB_MDH_DATA_DAILY_MONTHLY[DT] = CurrentYear - 1,
WEEKNUM = CurrentWeek -1 && TB_MDH_DATA_DAILY_MONTHLY[DT] = CurrentYear) )
[Unit Totals])
Solved! Go to Solution.
Hi @slow_turtle03 ,
I think you can try to create a calendar table as below and then create a measure.
Calendar =
VAR _BASIC =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Day", DAY ( [Date] ),
"WeekDay", WEEKDAY ( [Date], 2 ),
"WeekNum", WEEKNUM ( [Date], 2 )
)
VAR _ADD =
ADDCOLUMNS ( _BASIC, "WeekStart", [Date] - [WeekDay] + 1 )
RETURN
_ADD
Measure:
Measure =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[WeekStart] < MAX ( 'Calendar'[WeekStart] )
&& 'Calendar'[WeekStart]
>= MAX ( 'Calendar'[WeekStart] ) - 6 * 7
)
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @slow_turtle03 ,
I think you can try to create a calendar table as below and then create a measure.
Calendar =
VAR _BASIC =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Day", DAY ( [Date] ),
"WeekDay", WEEKDAY ( [Date], 2 ),
"WeekNum", WEEKNUM ( [Date], 2 )
)
VAR _ADD =
ADDCOLUMNS ( _BASIC, "WeekStart", [Date] - [WeekDay] + 1 )
RETURN
_ADD
Measure:
Measure =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[WeekStart] < MAX ( 'Calendar'[WeekStart] )
&& 'Calendar'[WeekStart]
>= MAX ( 'Calendar'[WeekStart] ) - 6 * 7
)
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check this:
https://www.vahiddm.com/post/weekly-time-intelligence-dax
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
User | Count |
---|---|
58 | |
21 | |
18 | |
16 | |
13 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |