Forum Discussion

kalpesh07's avatar
kalpesh07
Frequent Visitor
2 years ago
Solved

Dax query - rolling period

Hi All, I need help with DAX Formula to calculate Market Share. I've Master Data which is very clean but is a combination of Value & Volume by Segment,Manufacturer, Brand at MONTH Level. - Basic...
  • Alican_C's avatar
    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