Forum Discussion
Helper ICumulative amount calculation excluding expired dates
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.
Super UserHi,
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 ) )
)
streliHello, thank you very much for your reply :), I will let you know if it works.
- streli
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...
- Jihwan_Kim
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] ) )- streli
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.