Forum Discussion

streli's avatar
streli
Helper I
3 years ago
Solved

Cumulative amount calculation excluding expired dates

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's avatar
    Jihwan_Kim
    3 years ago

    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.

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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's avatar
      streli
      Helper I
      3 years ago

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

    • streli's avatar
      streli
      Helper I
      3 years ago

      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's avatar
        Jihwan_Kim
        Super User
        3 years ago

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