Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I've been following this guide to create a cumulative sum measure and it works perfectly fine, expect I want to be able to use it for all time resolutions I have in my custom hierachy.
I want to be able to see the cumulative sum for each month, week and day while I at the same time use a slicer to define the time-period I want to view the data.
My current measure looks like this:
acc_buy =
sumx(
FILTER(
ALLSELECTED(TimeQuery),
TimeQuery[YearMonthDay] <=MAX(TimeQuery[YearMonthDay])
),
[BUY_DIFF]
)
Below is the code i assumed would work:
acc_buy =
sumx(
FILTER(
ALLSELECTED(TimeQuery),
TimeQuery[YearMonthDay] <=MAX(TimeQuery[YearMonthDay]) ||
TimeQuery[WeekNumber] <=MAX(TimeQuery[WeekNumber]) ||
TimeQuery[Days] <=MAX(TimeQuery[Days])
),
[BUY_DIFF]
)
Hi @simber ,
Can you show some sample data and expected result to us?
Best regards,
Jay
@simber , the best way is to use it with date.
acc_buy =
sumx(
FILTER(
ALLSELECTED(TimeQuery),
TimeQuery[Date] <=MAX(TimeQuery[Date])
),
[BUY_DIFF]
)
The last one should work with &&.
acc_buy =
sumx(
FILTER(
ALLSELECTED(TimeQuery),
TimeQuery[YearMonthDay] <=MAX(TimeQuery[YearMonthDay]) &&
TimeQuery[WeekNumber] <=MAX(TimeQuery[WeekNumber]) &&
TimeQuery[Days] <=MAX(TimeQuery[Days])
),
[BUY_DIFF]
)
If you change <= to = at year level, it will reset total at year level
Before I go into detail how your code worked with my data, I might have to add that there are two measures both being cumulative summed and then added togheter.
So, I tried your code and I noticed it doesn't really work for a longer period of time, say 3 or 4 months and becomes discontinuous, see image below:
The upper graph is your code and the graph below is my code.
The original problem still remains though, that it doesn't aggregate the correct way even with your code.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |