power bi dax - expiry date problem calculate cumulative total

[streli]

hello everyone

I would like to calculate a cumulative amount using a dax measure so that this calculation only takes into account the volume associated with the expiry date until the expiry date, after which it... 

so if the expiry date is today and I see 5 units here, then tomorrow already ignore this for the cumulative, but on the line chart for yesterday's date take note

for June 7 I would like to see 26, for June 8 I would like to see 67, but for June 9 I would like to see 71 (and not 91...), because June 8 is the earlier (expired) date compared to June 9 and we don't need the 10-10 units we see on June 7 and June 8.

I would like a volume value to be included in the aggregate amount only as long as its expiry date is not earlier than dataday, so I would like to see 71 for 9 June and 92 for 10 June

thank you very mutch

[Anonymous]

Hi @streli,

You can try to use the following measure formula to get the cumulative total based on current date and expiry date:

Cumulative Volume =
CALCULATE (
    SUM ( Table1[volume] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        Table1[datekey] <= MAX ( Table1[datekey] )
            && Table1[datekey] <= Table1[expired date]
    )
)

Regards,

Xiaoxin Sheng

[streli]

Thank you very much for your reply, I tried, unfortunately it did not bring the expected result. It should be 71 for 9 June and 92 for 10 June...

[Anonymous]

Hi @streli,

After I double check on my formula, I find it seems only check the current records expired date values that may cause the rolling calculation on previous records.

For that scenario, I modify the formula to add a condition to check the max expired date of previous date keys , you can try it if helps:

Cumulative Volume =
VAR currDate =
    MAX ( Table1[datekey] )
VAR prevExpired =
    CALCULATE (
        MAX ( Table1[datekey] ),
        FILTER ( ALLSELECTED ( Table1 ), [datekey] < currDate )
    )
RETURN
    CALCULATE (
        SUM ( Table1[volume] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[datekey] <= currDate
                && AND ( Table1[datekey] <= Table1[expired date], Table1[datekey] > prevExpired )
        )
    )

Regards,

Xiaoxin Sheng

[streli]

Hi Xiaoxin Sheng,

I tried, unfortunately it did not work, but thank you very much for your reply 🙂

[streli]

Can anyone help me?