Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello datanuts!
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 WEEK | SALES WEEK | SALES TOTAL |
201950 | 201945 | 41,815 |
201950 | 201946 | 43,098 |
201950 | 201947 | 41,682 |
201950 | 201948 | 41,536 |
201951 | 201946 | 42,691 |
201951 | 201947 | 41,451 |
201951 | 201948 | 41,872 |
201951 | 201949 | 42,687 |
201952 | 201947 | 41,344 |
201952 | 201948 | 41,823 |
201952 | 201949 | 42,618 |
201952 | 201950 | 41,028 |
202001 | 201948 | 41,871 |
202001 | 201949 | 42,642 |
202001 | 201950 | 41,034 |
202001 | 201951 | 39,247 |
202002 | 201949 | 42,617 |
202002 | 201950 | 40,953 |
202002 | 201951 | 39,128 |
202002 | 201952 | 37,902 |
202003 | 201950 | 40,918 |
202003 | 201951 | 39,073 |
202003 | 201952 | 38,027 |
202003 | 202001 | 48,425 |
202004 | 201951 | 39,191 |
202004 | 201952 | 38,106 |
202004 | 202001 | 48,609 |
202004 | 202002 | 41,677 |
202005 | 201952 | 38,029 |
202005 | 202001 | 48,527 |
202005 | 202002 | 41,709 |
202005 | 202003 | 40,902 |
202006 | 202001 | 48,475 |
202006 | 202002 | 41,667 |
202006 | 202003 | 41,175 |
202006 | 202004 | 39,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.
Solved! Go to 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] )
)
)
Regards,
Xiaoxin Sheng
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
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)
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] )
)
)
Regards,
Xiaoxin Sheng
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.
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:
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |