Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alan7lp
Helper III
Helper III

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:

image.png

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:

 

image.png

 

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.

 

1 ACCEPTED SOLUTION

HI @alan7lp,

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

15.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

HI @alan7lp,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hello @v-shex-msft 

 

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)

 

image.png

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

HI @alan7lp,

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

15.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks @v-shex-msft ,

 

Works really good! I would need one last thing, please: as of now, it displays what I want but when it comes to TOTALS it's not accurate.

 

 Report.JPG

170,455 is correct for those 4 weeks. What I am after now is to have the that SUM + the sales quantities displayed in the previous weeks. In this case that SUM will be a total of 498,931

 

In a graphic way would be like:

real.JPG
Is this possible?

 

Thanks once again a lot for your support!

 

Best regards,

Alan.

Hi @alan7lp,

In fact, the graph is force achiec based on a few filter and conditions.  For this scenario, it is hard to accurately control the result displays on the total level. (I already use these filter that used to check total level)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you!

I thought it would be rather complicated to have such thing nevertheless the solution already works very well 🙂

 

Thanks again for your time!

 

Best regards,

Alan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.