Creating a measure filtering average sales for 12 month period prior to the last 12 Mos

[racope]

I currently have a measure (AvgSalesLast12Mos) which calculates average sales for the previous 12 month period.  The measure uses another measure called Avg Sales which is calculated by dividing Total Sales / Total Business Days.  The AvgSalesLast12Mos measure was created using DAX listed below.  I need to create another measure which calculates average sales for the 12 month period which preceeds the last 12 months used in AvgSalesLast12Mos.  Can I specify a different interval with the DATESINPERIOD command or do I have to use another method to calculate this new measure?

AvgSalesLast12Mos =

CALCULATE (

[Avg Sales],

DATESINPERIOD ('Date'[fulldatealternatekey], MAX ('Date'[fulldatealternatekey] ), -13, MONTH )

Thanks, 

RACope

[v-cazheng-msft · Accepted solution]

Hi @racope,

You may try this Measure.

AvgForDatesInPast =
VAR minDate =
    CALCULATE (
        MIN ( 'Date'[fulldatealternatekey] ),
        DATESINPERIOD (
            'Date'[fulldatealternatekey],
            MAX ( 'Date'[fulldatealternatekey] ),
            -13,
            MONTH
        )
    ) - 1
RETURN
    CALCULATE (
        [avg sales],
        DATESINPERIOD ( 'Date'[fulldatealternatekey], minDate, -13, MONTH )
    )

Then, the result should look like this.

Also, attach the pbix file as reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Whitewater100]

Hi:

You can try:

result = CALCULATE([avg sales],PARALLELPERIOD('Dates'[Date],-1,YEAR))  ..you can change how many years back, this will give you a 12 month answer. I hope this helps.