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.

- Basically, I want to be able the DAX formula to understand that Share of Market is the SUM of ONE Brand volumes of a given Month/Qtr/Year DATA POINT, vs. the total SEGMENT of that very Month/Quarter/Year. Which would then help me look at it/slice and dice by Brand/Mfg etc ...

 

I've created measure.

 

formula =
DIVIDE (
    SUM ( MarketData[Sales Value] ),
    CALCULATE (
        SUM ( MarketData[Sales Value] ),
        ALLSELECTED ( MarketData ),
        VALUES ( MarketData[SEGMENT] ),
        VALUES ( 'Calendar'[Date] )
    ),
    0
)

 

 

Can you help me with the same Measure but calculating the same as rolling period. I need help with Calculating on Rolling period Basis. MAT means last 12 Month.
So I've to calculate Sales% basis Last 12 month for every month like

Jan'24 MAT (Feb23-Jan24)
Feb'24 MAT (Mar23-Feb'24)
Mar'24 MAT (Apr23-Mar24)

 

  • Alican_C's avatar
    Alican_C
    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

     

1 Reply

  • Alican_C's avatar
    Alican_C
    Resolver II
    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