Rolling sum over time period with gap

[Nicole_E]

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

[lbendlin]

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)) 
    )

[Nicole_E]

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

[Anonymous]

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.