Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
We have a Power BI weekly line column chart which collect cumulative data and seems to be working great. There is one small issue with our reporting - everyweek we have to physically filter the current reporting week 😫.
Below is a example of the DAX code which produces the cumulative column chart, I have attempted to add a filter via. DAX but with no luck at all.
Any advice or suggestions with our dilemma would be greatly appreciated,
Thank you,
CumulativeChGrpAcc =
CALCULATE([TotalChgGrpAcc],
FILTER(ALLSELECTED(ChangeGrpAudit[Week]),
ChangeGrpAudit[Week] <= MAX (ChangeGrpAudit[Week])
)
)
TotalChgGrpAcc = CALCULATE(
COUNTA('ChangeGrpAudit'[CHANGE_ID]),
FILTER('ChangeGrpAudit', 'ChangeGrpAudit'[Year] = 2020
)
)
Solved! Go to Solution.
Hello Everyone,
Great News!!!! I was able to find a solution to my problem via. Youtube(Cumulative Totals). Being so new to DAX & Power BI I just didn't realize whe creating Cumulative values within a run chart the last value will progress until the end of the "X axis", which is what I was experiencing.
Below is a copy of the DAX code which addressed my individual problem and a link to the Youtube DAX Tutorial which explained Cumulative values and how to use Cumulative Totals Up To Specific Date.
NOTE: I had to alter my "Report Date" column by duplicating it into a separate column call "Date" changing the formatting to reflect date only.
Thanks everyone for your help
https://www.youtube.com/watch?v=JnhXyQ8eyuo
Cumulative Test value =
TotalChgGrpAcc = CALCULATE(
COUNTA('ChangeGrpAudit'[CHANGE_ID]),
FILTER('ChangeGrpAudit', 'ChangeGrpAudit'[Year] = 2020
)
)
Reporting Week = WEEKNUM(TODAY(),1)
VAR LastWeekDate = CALCULATE(LASTDATE(ChangeGrpAudit[Date]), ALL(ChangeGrpAudit))
RETURN
IF( SELECTEDVALUE(ChangeGrpAudit[Week] ) > 'Key Measures'[Reporting Week], BLANK(),
CALCULATE([TotalChgGrpAcc],
FILTER(ALLSELECTED(ChangeGrpAudit[Week]),
ChangeGrpAudit[Week] <= MAX (ChangeGrpAudit[Week])
)
)
)
Hi @dcg38524 ,
As Jan suggested, you could add a filter like WEEKNUM ( TODAY () ) to implement it.
current_week =
CALCULATE (
[TotalChgGrpAcc],
FILTER (
ALL ( 'ChangeGrpAudit'[Week], 'ChangeGrpAudit'[Year] ),
'ChangeGrpAudit'[Week] = WEEKNUM ( TODAY () )
&& 'ChangeGrpAudit'[Year] = YEAR ( TODAY () )
)
)
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you both, somehow I am still having trouble. Not having problems achieving the desired cumulative run chart results but the final week 8 cumulative figure of " 1213 " continues to post until week 52.
I attempted to modifiy the DAX code many times with your suggestions and others really stomped at this point.
Week | CumulativeChGrpAcc | |
1 | 49 | |
2 | 191 | |
3 | 335 | |
4 | 500 | |
5 | 642 | |
6 | 848 | |
7 | 1072 | |
8 | 1213 | Should have stop at week 8 |
9 | 1213 | |
10 | 1213 | |
.. | 1213 | |
.. | 1213 | |
.. | 1213 | |
52 | 1213 |
Total 2020 CumulativeChGrpAcc = CALCULATE(
COUNTA('ChangeGrpAudit'[CHANGE_ID]),
FILTER('ChangeGrpAudit', 'ChangeGrpAudit'[Year] = 2020)
)
CumulativeChGrpAcc =
CALCULATE([Total 2020 CumulativeChGrpAcc],
FILTER(ALLSELECTED(ChangeGrpAudit[Week]),
ChangeGrpAudit[Week] <= MAX (ChangeGrpAudit[Week])
)
)
Hello Everyone,
Great News!!!! I was able to find a solution to my problem via. Youtube(Cumulative Totals). Being so new to DAX & Power BI I just didn't realize whe creating Cumulative values within a run chart the last value will progress until the end of the "X axis", which is what I was experiencing.
Below is a copy of the DAX code which addressed my individual problem and a link to the Youtube DAX Tutorial which explained Cumulative values and how to use Cumulative Totals Up To Specific Date.
NOTE: I had to alter my "Report Date" column by duplicating it into a separate column call "Date" changing the formatting to reflect date only.
Thanks everyone for your help
https://www.youtube.com/watch?v=JnhXyQ8eyuo
Cumulative Test value =
TotalChgGrpAcc = CALCULATE(
COUNTA('ChangeGrpAudit'[CHANGE_ID]),
FILTER('ChangeGrpAudit', 'ChangeGrpAudit'[Year] = 2020
)
)
Reporting Week = WEEKNUM(TODAY(),1)
VAR LastWeekDate = CALCULATE(LASTDATE(ChangeGrpAudit[Date]), ALL(ChangeGrpAudit))
RETURN
IF( SELECTEDVALUE(ChangeGrpAudit[Week] ) > 'Key Measures'[Reporting Week], BLANK(),
CALCULATE([TotalChgGrpAcc],
FILTER(ALLSELECTED(ChangeGrpAudit[Week]),
ChangeGrpAudit[Week] <= MAX (ChangeGrpAudit[Week])
)
)
)
Hi @dcg38524
I assume there is a relation with the todays date for your Reporting Week.
So you could you the TODAY() function to set you current week selection in the measure.
Jan
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |