Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I have a matrix where Day on X-Axis, Year and Quarter on Y-axis. Cumulated amount on Values.
wherever there is a blank, it should take the previous amount instead of displaying blank. for example, on Day 2 for 2024 Q1, it should show 787,544. On day 6,7,8 for 2023 Q4, it should show 10,201,258.
below is the dax for cumulative amount.
Cumulative Amount =
var maxnum = MAX('Sheet1'[Day])
var tmp = FILTER(ALL(Sheet1[Day]),'Sheet1'[Day]<=maxnum)
return CALCULATE(SUM('Sheet1'[Amount]),tmp)
Please help
You haven't shared where the "year - quarter" column comes from. If that column is also in that same table, then there's no way to fix this with a measure. (try testing it with a dummy measure, like "Test Amount = 1"; if you still have blanks in your matrix than measures can't fix this).
So you probaly need to remodel a bit and use a proper date dimension.
Hey thanks for the advise! I have created proper date dimension and now the dummy measure is giving no blanks. How can I change the measure now? Thanks !
Well, To help with that I would need information on the date dimension and the relationship(s) with the fact table....
not authorized for that link.
hi, @sam_gift
try below
Cumulative Amount =
sumx(
filter(
Sheet1,
'Sheet1'[Day]<=max('Sheet1'[Day])
),
'Sheet1'[Amount]
)
User | Count |
---|---|
57 | |
21 | |
19 | |
18 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |