Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I am a fairly new user of PowerBI. I am stuck on this particular problem for 2 days. Any help or pointers would be appreciated.
I am working on basic sales wherein I have created a measure of Total Transactions as:
Total Transactions = DISTINCTCOUNT('Sales Data'[OrderNumber])
Now, I want to create another measure called Cumulative Transactions Selected as:
Cumulative Transactions Selected =
CALCULATE (
[Total Transactions],
FILTER( ALL ( 'Dates'[Date] ),
'Dates'[Date] <= MAX ( 'Dates'[Date] )))
It works fine only if I am selecting the first quarter:
Things go awry once I select other quarters:
I tried different variations of the formula like using ALLSELECTED so that it takes all the selected filters but I am not getting the desired results.
Thanks in advance for any help :).
Update: Use the following formula:
Cumulative Transactions Selected =
CALCULATE (
[Total Transactions](
FILTER( ALL ( 'Dates'[Date] ),
Dates[Date] >= MIN ('Dates'[Date]) && Dates[Date] <= MAX( 'Dates'[Date]) )))
Works well when I select consecutive quarters but fails when I select non- consecutive quarters :(.
Hi @janen,
Can you share the pbix file? You can mask the private parts first. The formula seems good. It could be something wrong with the data model.
Best Regards,
Dale
Just an update:
CALCULATE ([Total Transactions],
FILTER (ALL(Dates[Date]),
Dates[Date] <= MAX( Dates[Date])),
VALUES(Dates[Quarter & Year]))
I tried the above formula. This works fine but it resets after every quarter. So, if I select QUARTER1 and QUARTER2 it will reset my calculations after QUARTER1 and restart again.
User | Count |
---|---|
77 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |