Dax measure to correctly calculate previous week category and subtotal

Question

I am using a matrix table in powerbi to show previous week totals for different areas (categories). I have the majority of it working but I am not able to correctly get the subtotals on the table working. I believe it has to do with the filtering that I am using - i have been unable to correct it.

screen capture

As you can see my Total for week 24 previous is missing

Dax code is:

VAR Area =
    MAX ( 'SumTable'[Area Name] )
VAR CurrentWeek =
    SELECTEDVALUE ( SumTable[WeekofYear] )
VAR CurrentYear =
    SELECTEDVALUE ( SumTable[Year] )
VAR MaxWeekNumber =
    CALCULATE ( MAX ( SumTable[WeekofYear] ), ALL ( SumTable ) )
RETURN
    IF (
        HASONEVALUE ( SumTable[Area Name] ),
        SUMX (
            FILTER (
                ALL ( SumTable ),
                IF (
                    CurrentWeek = 1,
                    SumTable[WeekofYear] = MaxWeekNumber
                        && SumTable[Year] = CurrentYear - 1
                        && SumTable[Area Name] = Area,
                    SumTable[WeekofYear] = CurrentWeek - 1
                        && SumTable[Year] = CurrentYear
                        && SumTable[Area Name] = Area
                )
            ),
            SumTable[BOE]
        ),
        SUMX (
            FILTER (
                ALLSELECTED ( SumTable ),
                IF (
                    CurrentWeek = 1,
                    SumTable[WeekofYear] = MaxWeekNumber
                        && SumTable[Year] = CurrentYear - 1,
                    SumTable[WeekofYear] = CurrentWeek - 1
                        && SumTable[Year] = CurrentYear
                )
            ),
            SumTable[BOE]
        )
    )

Data Table: Example Table Format

Thank you, first time poster!

B

mxix mxix · Accepted Answer · 2020-11-08T22:01:50

I would start by spliting my data table from my date table.

And I guess you don't need to ALL the whole table, just the columns for year and weeknumber and keep the Area in context, that way you don't have to bother if HASONEVALUE it will just work.

SELECTEDVALUE only returns if only a single value for that column is in context, not the case for totals and subtotals.

MyMeasure = 
VAR CurrentWeek =
    MAX( SumTable[WeekofYear] )
VAR CurrentYear =
    MAX( SumTable[Year] )
VAR MaxWeekNumber =
    CALCULATE ( MAX ( SumTable[WeekofYear] ), SumTable[Year] = CurrentYear-1  )
RETURN
    IF(
       CurrentWeek = 1,
       CALCULATE(
           SUM(SumTable[BOE]),
           FILTER (
               ALL ( SumTable[Year],SumTable[WeekofYear]),
               SumTable[WeekofYear] = MaxWeekNumber
               && SumTable[Year] = CurrentYear - 1
           )
       ),
       CALCULATE (
           SUM ( SumTable[BOE] ),
           FILTER (
               ALL ( SumTable[WeekofYear] ),
               SumTable[WeekofYear] = CurrentWeek-1
           )
       )
)

I did not have a chance to confirm this code.

Dax measure to correctly calculate previous week category and subtotal

1 Answers