Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic total sales with moving week

Hello datanuts!

v-lionel-msft 

 

I need help to create some DAX that will help me to sumarize the following data in a proper way. I receive a weekly report with 4 weeks history of sales. If a select X report week, I will always see a total of sales of those 4 weeks but I would like to add to that total the "missing" week from the previous report (last image will explain it better)

 

This is how the data is displayed in my report:

And this is how my data is manipulated in the query:

REPORT WEEKSALES WEEKSALES TOTAL
20195020194541,815
20195020194643,098
20195020194741,682
20195020194841,536
20195120194642,691
20195120194741,451
20195120194841,872
20195120194942,687
20195220194741,344
20195220194841,823
20195220194942,618
20195220195041,028
20200120194841,871
20200120194942,642
20200120195041,034
20200120195139,247
20200220194942,617
20200220195040,953
20200220195139,128
20200220195237,902
20200320195040,918
20200320195139,073
20200320195238,027
20200320200148,425
20200420195139,191
20200420195238,106
20200420200148,609
20200420200241,677
20200520195238,029
20200520200148,527
20200520200241,709
20200520200340,902
20200620200148,475
20200620200241,667
20200620200341,175
20200620200439,138

 

And this is what I would like to do:

 

 

For example, if I select REPORT WEEK 202006, my result should be the SUM of the cells highlighted in blue and same logic for whichever selected report week. If I select REPORT WEEK 201951 then my result should be the 4 SALES WEEK history (201945 + 201946 + 201947 + 201948)  of that file + the first SALES WEEK in last report (201945 )

 

I hope it's clear enough. If necessary I can provide the pbix file.

 

Thanks a lot in advance,

 

Best regards.

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous,

    So you mean you want to display selected report week and the first week of each row? If this is a case, you can refer to following measure formula:

    Measure =
VAR currRW =
    MAX ( Table[REPORT WEEK] )
VAR currSW =
    MAX ( Table[SALES WEEK] )
VAR week =
    CALCULATETABLE (
        VALUES ( Table[SALES WEEK] ),
        FILTER ( ALLSELECTED ( Table ), [REPORT WEEK] <= MAX ( Selector[REPORT WEEK] ) ),
        VALUES ( Table[REPORT WEEK] )
    )
RETURN
    IF (
        currRW IN ALLSELECTED ( 'Selector'[REPORT WEEK] )
            || currSW = MINX ( week, [SALES WEEK] )
                && ISFILTERED ( Table[SALES WEEK] ),
        CALCULATE (
            SUM ( Table[SALES TOTAL] ),
            ALLSELECTED ( Table ),
            VALUES ( Table[REPORT WEEK] ),
            VALUES ( Table[SALES WEEK] )
        )
    )

    Regards,

    Xiaoxin Sheng

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous,

    So you mean you only want slicer to hide other records with breaking rolling calculations?

    If this is a case, I'd like to suggest you create a new table as source of the slicer. Then you can add if statement to your rolling formula to replace records that not include in the selected range to blank, power bi will auto-hide these blank parts.

    Measure =
VAR currRW =
    MAX ( 'Table'[REPORT WEEK] )
RETURN
    IF (
        currRW IN ALLSELECTED ( 'Selector'[REPORT WEEK] ),
        'formual'
    )

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello Anonymous 

       

      Thanks for your reply! Actually it's not that what I want only. I don't want just to hide previous weeks and display only current week. What I need is to add to the current 4 weeks history sales (latest report uploaded) the previous sales week which do not repeat in the current one (that's why the cell highligted in blue)

       

      As you can see, if I select (row) week 202006, the history of sales is only 202004, 202003, 202002, 202001. Previous to that is not available on that week report but it's in the older versions (all blue cells).

       

      Not sure if my explanation makes sense but in case it doesn't, let me know and I will try to explain myself better.

       

      Thanks once again,

       

      Cheers

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI Anonymous,

        So you mean you want to display selected report week and the first week of each row? If this is a case, you can refer to following measure formula:

        Measure =
VAR currRW =
    MAX ( Table[REPORT WEEK] )
VAR currSW =
    MAX ( Table[SALES WEEK] )
VAR week =
    CALCULATETABLE (
        VALUES ( Table[SALES WEEK] ),
        FILTER ( ALLSELECTED ( Table ), [REPORT WEEK] <= MAX ( Selector[REPORT WEEK] ) ),
        VALUES ( Table[REPORT WEEK] )
    )
RETURN
    IF (
        currRW IN ALLSELECTED ( 'Selector'[REPORT WEEK] )
            || currSW = MINX ( week, [SALES WEEK] )
                && ISFILTERED ( Table[SALES WEEK] ),
        CALCULATE (
            SUM ( Table[SALES TOTAL] ),
            ALLSELECTED ( Table ),
            VALUES ( Table[REPORT WEEK] ),
            VALUES ( Table[SALES WEEK] )
        )
    )

        Regards,

        Xiaoxin Sheng