Power bi calculate sum taking into account the expiry date

[streli]

Hi

I have a dataday and an expiry date column for a year, each dataday has an expiry date and a unit value.

How can I calculate a cumulative amount for the unit values, so that the calculation skips the datadays and unit values with an earlier expiry date than the dataday?

For example, there is an expiry date of 20 June for a 20 June dataday and therefore I don't want it to be included in the cumulative amount of 21 June.

Thank you very much!

[v-jianboli-msft · Accepted solution]

Hi @streli ,

Please try:

Amount Cumulative = SUMX(FILTER(ALL(data),[DataDay]<=SELECTEDVALUE(data[DataDay])&&[ExpiryDay]>=SELECTEDVALUE(data[DataDay])),[Volumen])

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[streli]

Hi Jianbo Li, very good, thank you very much for you reply, it works!!!

[Sahir_Maharaj]

Hello @streli,

Can you please try: 

Cumulative Amount =
VAR CurrentDate = 'Table'[dataday]
RETURN
    CALCULATE(
        SUM('Table'[unit value]),
        'Table'[expiry date] >= CurrentDate
    )

Do not hesitate to let me know if you might need further assistance.

[streli]

hello

thank you very much for your reply!

unfortunately after VAR it does not recognize the DataDay field in the data table

do you have any more ideas?

thank you very much