Forum Discussion
Dax query - rolling period
- 2 years ago
Hi,
You need to adjust your measure to take into account the last 12 months dynamically.
First, create a measure that calculates the sales over the last 12 months.Sales MAT =CALCULATE(SUM(MarketData[Sales Value]),DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH))
Next, create a measure that calculates the total segment sales over the last 12 months.
Total Segment Sales MAT =CALCULATE(SUM(MarketData[Sales Value]),ALLEXCEPT(MarketData,MarketData[SEGMENT]),DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH))
Finally, create the measure to calculate the Market Share over the last 12 months.
Market Share MAT =DIVIDE([Sales MAT],[Total Segment Sales MAT],0)
Make sure your date table has continuous months so the MAT calculation can roll over each month correctly
Hi,
You need to adjust your measure to take into account the last 12 months dynamically.
First, create a measure that calculates the sales over the last 12 months.
Sales MAT =CALCULATE(SUM(MarketData[Sales Value]),DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH))
Next, create a measure that calculates the total segment sales over the last 12 months.
Total Segment Sales MAT =CALCULATE(SUM(MarketData[Sales Value]),ALLEXCEPT(MarketData,MarketData[SEGMENT]),DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH))
Finally, create the measure to calculate the Market Share over the last 12 months.
Market Share MAT =DIVIDE([Sales MAT],[Total Segment Sales MAT],0)
Make sure your date table has continuous months so the MAT calculation can roll over each month correctly