Forum Discussion

streli's avatar
streli
Icon for Helper I rankHelper 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 ExpiryD...
  • 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.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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
Icon for Helper I rankHelper I
3 years ago

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