topic Rolling sum over time period with gap in DAX Commands and Tips

Rolling sum over time period with gap

Nicole_E — Mon, 03 Jun 2024 13:32:56 GMT

Hello PowerBi Community,

I would like to calculate the rolling 5 years sum of sales while excluding year 3.

Hence, the sum of years 1+2 and years 4+5.

Is there a way to calculate this using only one calculate function?

Currently I have tried to use the following code:

VAR LastDateToUse = MAX ( 'CALENDAR'[Date] )

VAR Period_1_to_2_years = DATESINPERIOD ( 'CALENDAR'[Date], LastDateToUse, - 2, YEAR )

VAR Period_4_to5_years = DATESINPERIOD ( 'CALENDAR'[Date], EDATE(LastDateToUse,-36), - 2, YEAR )

RETURN

CALCULATE (

SUMX (

Sales_ProductSerial,

Sales_ProductSerial[Unit Wholesale]

OR(Period_1_to_2_years, Period_4_to5_years)

)

However, the calculate filter function does not accept an or statement on the DATESINPERIOD function.

Thank you in advance for your help!

Kind regards,

Nicole

Re: Rolling sum over time period with gap

lbendlin — Mon, 03 Jun 2024 22:24:29 GMT

Another "creative" way would be to calculate the last five years and then subtract the value for year 3.

Using CALCULATE and aggregation functions in the same formula is a mild red flag. Usually you use one or the other, not both.

CALCULATE ( SUM (Sales_ProductSerial[Unit Wholesale] ), CALENDAR[Date] IN CALENDAR(EDATE(LastDateToUse,-60)+1,LastDateToUse) CALENDAR[Date] NOT IN CALENDAR(EDATE(LastDateToUse,-36)+1,EDATE(LastDateToUse,-24)) )

Re: Rolling sum over time period with gap

Nicole_E — Tue, 04 Jun 2024 11:46:09 GMT

Thank you for taking a look.

I have rewritten your suggestion a little bit:

VAR LastDateToUse =

EDATE ( MAX ( 'CALENDAR'[Date] ), -12 )

return

CALCULATE ( SUM (Sales_ProductSerial[Unit Wholesale] ),

'CALENDAR'[Date] IN CALENDAR(EDATE(LastDateToUse,-60)+1,LastDateToUse)

&& NOT'CALENDAR'[Date] IN CALENDAR(EDATE(LastDateToUse,-36)+1,EDATE(LastDateToUse,-24))

)

It is resulting in the following error: "the start date or end date in Calendar function can not be a blank value". Any idea what is causing that?

Further, there are is another requirements that I did not mention before, but does limit the options for solving this. I multiple the unit wholesale by a value of the related parts table in the sumx.

For year 3 I am using another related value than for the 1+2+4+5 years, which is why I need to break them up. I have solved it using the below code which looks very inefficient.

Expected Sales (units) =

// Year 1+2+4+5 100H, Year 3 300H

VAR LastDateToUse =

MAX ( 'CALENDAR'[Date] )

VAR Period_1_to_2_years =

DATESINPERIOD ( 'CALENDAR'[Date], LastDateToUse, - 2, YEAR )

VAR Period_3_year =

DATESINPERIOD ( 'CALENDAR'[Date], EDATE ( LastDateToUse, -24 ), - 1, YEAR )

VAR Period_4_to_5_years =

DATESINPERIOD ( 'CALENDAR'[Date], EDATE ( LastDateToUse, -36 ), - 2, YEAR )

VAR Calc_years_1_2 =

CALCULATE (

SUMX ( Sales_ProductSerial, Sales_ProductSerial[Unit Wholesale]* RELATED(Zp_Maitenance[100 H]) ),

Period_1_to_2_years

)

VAR Calc_years_3 =

CALCULATE (

SUMX ( Sales_ProductSerial, Sales_ProductSerial[Unit Wholesale] * RELATED(Zp_Maitenance[300 H]) ),

Period_3_year

)

VAR Calc_years_4_5 =

CALCULATE (

SUMX ( Sales_ProductSerial, Sales_ProductSerial[Unit Wholesale] * RELATED(Zp_MaitenanceEngine[100 H]) ),

Period_4_to_5_years

)

RETURN

Calc_years_1_2 + Calc_years_3 + Calc_years_4_5

Re: Rolling sum over time period with gap

Anonymous — Wed, 19 Jun 2024 04:23:20 GMT

Hi,Nicole_E
May I ask if your problem has been solved, I see that you have found a suitable solution yourself, if your problem has been solved, please mark the suggestions you have provided as solutions.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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