Previous 6 week totals by week

[slow_turtle03]

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])

[v-rzhou-msft · Accepted 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.

[VahidDM]

HI @slow_turtle03 

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

LinkedIn | Twitter | Blog | YouTube