Latest Enterprise DNA Initiatives

Cumulative MTD total up to Previous Week

Hi all,

I would like your advice if it is possible to write a statement on cumulative total that sums up only to the Sunday of last week.

I have added the Date Query table and would want the statement to sum as follows:
MTD Sales = Calculate Sum of Quantity Sold from 01-June up to 23 June. (WK25, Sunday).

This statement would have to exclude any Quantity sold on the current week 26.

Regards
Hidayat

Try not to be scared by the size, but I tried to as many variables were needed to understand the concept. Always when we have to work with weeks in Power BI is harder… but, once understand some concepts in DAX, then it gets easier.

Measure =
VAR LastDay =
    TODAY () // Can change for Max(fact[Date]) or Max(Calendar[Date]) . depends of your analysis
VAR CurrentYear =
    YEAR ( LastDay )
VAR MaxWeek =
    VALUE ( LOOKUPVALUE ( Calendar[WeekNum]; Calendar[Date]; LastDay ) ) // Week of the year. example: 26
VAR YearPreviousWeek =
    IF (
        MaxWeek = 1;
        // Checking if the week is the first of the Year
        CurrentYear - 1;
        // Changeing to get Last Week of last Year. but if you have no sum() for the first week fo the year. replace the -1 with 0 
        CurrentYear
    )
VAR PreviousWeek =
    IF (
        MaxWeek = 1;
        // Checking if the week is the first of the Year
        CALCULATE (
            MAX ( Calendar[WeekNum] );
            VALUE ( Calendar[Year] ) = ( CurrentYear - 1 )
        );
        // Samething. if you have no sum(). replace with an non exist week or blank() 
        MaxWeek - 1
    )
VAR LastDatePreviousWeek =
    CALCULATE (
        MAX ( Calendar[Date] );
        FILTER (
            ALL ( Calendar[Date]; Calendar[Year]; Calendar[WeekNum] );
            VALUE ( Calendar[Year] ) = YearPreviousWeek
                && VALUE ( Calendar[WeekNum] ) <= PreviousWeek
        )
    )
VAR ResultAsCard =
    // To show as a Card (Fixed Amount of the calculation
    CALCULATE (
        SUM ( FactSales[Amount] );
        FILTER (
            ALL ( Calendar );
            VALUE ( Calendar[Year] ) = YearPreviousWeek
                && Calendar[Date] <= LastDatePreviousWeek
        )
    )
RETURN
    IF (
        MAX ( Calendar[Date] ) <= LastDatePreviousWeek;
        // To show Acumulating (If you segmenting by date.week.month.etc… )
        CALCULATE (
            SUM ( FactSales[Amount] );
            FILTER (
                ALL ( Calendar );
                VALUE ( Calendar[Year] ) = YearPreviousWeek
                    && Calendar[Date] <= MAX ( Calendar[Date] )
            )
        );
        //Checking if is a selection in Month/Week (and you can add more much… ) to only show this calculation when is in total/card
        IF (
            OR ( HASONEVALUE ( Calendar[Month] )HASONEVALUE ( Calendar[WeekNum] ) );
            BLANK ();
            ResultAsCard
        )
    )

Is it possible to load in a demo file that you are working on. I’m also happy to have a look and suggest a formula.

Thanks
Sam