default to latest week

[Anonymous]

Hi there

In the screenshot below, i need the weekly difference figure in green to default to the difference between the 2 most recent weeks i.e. september week 2 and september week 3. At the moment, it's defaulting to the final 2 weeks in august.

The weekly difference is a MEASURE.

Can I please get some help with this?

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

From your screenshot, I think the week no. in the visual is a custom week logic, that every almost month has 5 weeks.

So you may try this formula:

Measure 2 = 
VAR maxmonth =
    CALCULATE ( MAX ( 'Table'[YearMonth] ) )
VAR maxweek =
    CALCULATE (
        MAX ( 'Table'[Week] ),
        FILTER ( 'Table', 'Table'[YearMonth] = maxmonth )
    )
RETURN
    VAR lastweek =
        IF (
            maxweek = 1
                && MAX ( 'Table'[Month] ) = 1,
            CALCULATE (
                MAX ( 'Table'[Week] ),
                FILTER ( ALL ( 'Table' ), 'Table'[YearMonth] = maxmonth - 89 )
            ),
            IF (
                maxweek = 1,
                CALCULATE (
                    MAX ( 'Table'[Week] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[YearMonth] = maxmonth - 1 )
                ),
                maxweek - 1
            )
        )
    RETURN
        CALCULATE (
            SUM ( 'Table'[Field1] ),
            FILTER ( 'Table', 'Table'[YearMonth] = maxmonth && 'Table'[Week] = maxweek )
        )
            - IF (
                MAX ( 'Table'[Month] ) = 1
                    && maxweek = 1,
                CALCULATE (
                    SUM ( 'Table'[Field1] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[YearMonth] = maxmonth - 89
                            && 'Table'[Week] = lastweek
                    )
                ),
                IF (
                    maxweek = 1,
                    CALCULATE (
                        SUM ( 'Table'[Field1] ),
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[YearMonth] = maxmonth - 1
                                && 'Table'[Week] = lastweek
                        )
                    ),
                    CALCULATE (
                        SUM ( 'Table'[Field1] ),
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[YearMonth] = maxmonth
                                && 'Table'[Week] = lastweek
                        )
                    )
                )
            )

Result:

and here is sample pbix, please try it,.

Best Regards,

Lin

[Anonymous]

You can include two more field as 'Weeknum' & 'Year' and use the below DAX to get the difference between current and last week's value

VAR MaxWeekNum = MAX('Table'[WeekNum])

VAR Max2ndWeekNum = IF(MAX('Table'[WeekNum])=1,CALCULATE(MAX('Table'[WeekNum]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])-1)),MAX('Table'[WeekNum])-1)

VAR Max2ndWeekNumYear = IF(MAX('Table'[WeekNum])=1,MAX('Table'[Year])-1,MAX('Table'[Year]))

RETURN CALCULATE(SUM('Table'[Field1]), FILTER('Table','Table'[WeekNum]= MAX('Table'[WeekNum])) -
CALCULATE(SUM('Table'[Field1]), FILTER('Table','Table'[WeekNum]=Max2ndWeekNum && 'Table'[Year] =Max2ndWeekNumYear))