Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |