topic Power bi calculate sum taking into account the expiry date in DAX Commands and Tips

Power bi calculate sum taking into account the expiry date

streli — Fri, 23 Jun 2023 13:59:52 GMT

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!

Re: Power bi calculate sum taking into account the expiry date

Sahir_Maharaj — Fri, 23 Jun 2023 20:36:19 GMT

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.

Re: Power bi calculate sum taking into account the expiry date

streli — Sat, 24 Jun 2023 14:29:23 GMT

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

Re: Power bi calculate sum taking into account the expiry date

v-jianboli-msft — Thu, 29 Jun 2023 07:02:33 GMT

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.

Re: Power bi calculate sum taking into account the expiry date

streli — Thu, 29 Jun 2023 08:55:45 GMT

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