topic Creating a measure filtering average sales for 12 month period prior to the last 12 Mos in DAX Commands and Tips

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

racope — Fri, 13 May 2022 14:11:41 GMT

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

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

Whitewater100 — Fri, 13 May 2022 19:16:45 GMT

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.

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

v-cazheng-msft — Wed, 18 May 2022 06:23:37 GMT

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