topic Re: Cumulative amount calculation excluding expired dates in DAX Commands and Tips

Cumulative amount calculation excluding expired dates

streli — Sun, 25 Jun 2023 12:31:08 GMT

Hello!

I have a data table with 3 colums: DataDay, UnitsSold, ExpiryDate.

I would like to calculate cumulative amount for the units sold, but exclude from the calculation where above ExpiryDate < DataDay, so I would like to see 8 instead of 11 for the DataDay of June 10, since June 9 is an earlier date than June 10.

It is very important that someone can help me.

Thank you very much!!!

Re: Cumulative amount calculation excluding expired dates

Jihwan_Kim — Sun, 25 Jun 2023 13:08:03 GMT

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a measure and a visualization.
I hope the below can provide some ideas on how to create a solution for your datamodel.

Expected result measure: = VAR _currentdate = MAX ( 'Calendar'[Date] ) VAR _expirydate = SUMMARIZE ( FILTER ( ALL ( Data ), Data[ExpiryDate] >= _currentdate ), 'Calendar'[Date] ) RETURN CALCULATE ( SUM ( Data[UnitSold] ), WINDOW ( 1, ABS, 0, REL, _expirydate, ORDERBY ( 'Calendar'[Date], ASC ) ) )

Re: Cumulative amount calculation excluding expired dates

streli — Sun, 25 Jun 2023 17:03:13 GMT

Hello, thank you very much for your reply :), I will let you know if it works.

Re: Cumulative amount calculation excluding expired dates

streli — Mon, 26 Jun 2023 13:21:05 GMT

Hi Jihwan_Kim, unfortunately I can't attach a .pbix file, but I'm trying to show you that I failed:

Unfortunately, the DAX formula included the 10 volumes of June 7 (expiry date June 8 ) and the 10 volumes of June 8 ( expiry date June 8 ) in the sum of June 9, although it should not have done so, because the rule is to exclude if expiry date < dataday...

Re: Cumulative amount calculation excluding expired dates

Jihwan_Kim — Tue, 27 Jun 2023 15:53:27 GMT

Hi,

Could you please check the below measure, just in case if I understood your question correctly?

Expected result measure: = VAR _currentdate = MAX ( 'Calendar'[Date] ) VAR _expirydate = FILTER ( ALL ( Data ), Data[ExpiryDate] >= _currentdate ) RETURN IF ( NOT ISBLANK ( [Unit sold measure:] ), SUMX ( FILTER ( _expirydate, Data[Date] <= _currentdate ), Data[UnitSold] ) )

Re: Cumulative amount calculation excluding expired dates

streli — Tue, 27 Jun 2023 17:20:14 GMT

Hi Jihwan_Kim, thank you very much for your reply :), unfortunately only the volume 92 of 10 June is correct, and we see this number for every day, which is not good. But for 9 June the correct value is 71, for 8 June: 67 and for 7 June: 26 is the correct.

Re: Cumulative amount calculation excluding expired dates

Jihwan_Kim — Tue, 27 Jun 2023 19:14:37 GMT

Hi,

Please open the file that I have attached in my last reply.

And in my opnion, I think your visualizatoin is using Date from Data table, but Date has to be from Calendar table.
Just in case, I am attaching the file again.
Thank you.

Re: Cumulative amount calculation excluding expired dates

streli — Tue, 27 Jun 2023 19:45:36 GMT

hi Jihwan_Kim, you are right, it works and thank you very much!!! 🙂