topic Re: Take previous Cumulated Value if blank matrix visual in DAX Commands and Tips

Take previous Cumulated Value if blank matrix visual

sam_gift — Thu, 04 Jan 2024 22:18:22 GMT

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

Re: Take previous Cumulated Value if blank matrix visual

Dangar332 — Fri, 05 Jan 2024 08:04:48 GMT

hi, sam_gift

try below

Cumulative Amount = sumx( filter( Sheet1, 'Sheet1'[Day]<=max('Sheet1'[Day]) ), 'Sheet1'[Amount] )

Re: Take previous Cumulated Value if blank matrix visual

sjoerdvn — Fri, 05 Jan 2024 09:12:53 GMT

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.

Re: Take previous Cumulated Value if blank matrix visual

sam_gift — Fri, 05 Jan 2024 15:32:20 GMT

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 !

Re: Take previous Cumulated Value if blank matrix visual

sjoerdvn — Fri, 05 Jan 2024 16:33:19 GMT

Well, To help with that I would need information on the date dimension and the relationship(s) with the fact table....

Re: Take previous Cumulated Value if blank matrix visual

sam_gift — Fri, 05 Jan 2024 16:46:34 GMT

Hi Here is the sample data I am working with. File

Re: Take previous Cumulated Value if blank matrix visual

sjoerdvn — Mon, 08 Jan 2024 08:58:07 GMT

not authorized for that link.