topic Re: power bi dax - expiry date problem calculate cumulative total in DAX Commands and Tips

power bi dax - expiry date problem calculate cumulative total

streli — Wed, 21 Jun 2023 14:30:15 GMT

hello everyone

I would like to calculate a cumulative amount using a dax measure so that this calculation only takes into account the volume associated with the expiry date until the expiry date, after which it...

so if the expiry date is today and I see 5 units here, then tomorrow already ignore this for the cumulative, but on the line chart for yesterday's date take note

for June 7 I would like to see 26, for June 8 I would like to see 67, but for June 9 I would like to see 71 (and not 91...), because June 8 is the earlier (expired) date compared to June 9 and we don't need the 10-10 units we see on June 7 and June 8.

I would like a volume value to be included in the aggregate amount only as long as its expiry date is not earlier than dataday, so I would like to see 71 for 9 June and 92 for 10 June

thank you very mutch

Re: power bi dax - expiry date problem calculate cumulative total

streli — Thu, 22 Jun 2023 12:18:00 GMT

Can anyone help me?

Re: power bi dax - expiry date problem calculate cumulative total

Anonymous — Tue, 27 Jun 2023 01:43:43 GMT

Hi streli,

You can try to use the following measure formula to get the cumulative total based on current date and expiry date:

Cumulative Volume = CALCULATE ( SUM ( Table1[volume] ), FILTER ( ALLSELECTED ( Table1 ), Table1[datekey] <= MAX ( Table1[datekey] ) && Table1[datekey] <= Table1[expired date] ) )

Regards,

Xiaoxin Sheng

Re: power bi dax - expiry date problem calculate cumulative total

streli — Tue, 27 Jun 2023 12:53:03 GMT

Thank you very much for your reply, I tried, unfortunately it did not bring the expected result. It should be 71 for 9 June and 92 for 10 June...

Re: power bi dax - expiry date problem calculate cumulative total

Anonymous — Wed, 28 Jun 2023 06:29:35 GMT

Hi streli,

After I double check on my formula, I find it seems only check the current records expired date values that may cause the rolling calculation on previous records.

For that scenario, I modify the formula to add a condition to check the max expired date of previous date keys , you can try it if helps:

Cumulative Volume = VAR currDate = MAX ( Table1[datekey] ) VAR prevExpired = CALCULATE ( MAX ( Table1[datekey] ), FILTER ( ALLSELECTED ( Table1 ), [datekey] < currDate ) ) RETURN CALCULATE ( SUM ( Table1[volume] ), FILTER ( ALLSELECTED ( Table1 ), Table1[datekey] <= currDate && AND ( Table1[datekey] <= Table1[expired date], Table1[datekey] > prevExpired ) ) )

Regards,

Xiaoxin Sheng

Re: power bi dax - expiry date problem calculate cumulative total

streli — Wed, 28 Jun 2023 20:22:29 GMT

Hi Xiaoxin Sheng,

I tried, unfortunately it did not work, but thank you very much for your reply 🙂