Cumulative amount calculation excluding expired dates

[streli]

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!!!

[Jihwan_Kim]

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 ) )
    )

[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.

[Jihwan_Kim · Accepted solution]

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.

[streli]

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

[streli]

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