Price Elasticity

[FischerEric86]

I need to create a price elasticity calculation.  We want to be able to select any given period, ex: 202402 (year month) which would be the start of our new price and quantity for 3 months. So it would be 202402, 202403, 202404 and then our prior price and quantities would be the trailing 3 periods, 202401, 202312, 202311. 

The key for this to work is that I want to be able to select any starting period that would define the start of our new pricing and have the calculation know to adjust and include the 2 additional months forward and three months back.  

this way we can select any single period in time and see the price elasticity. 

[Gaga_Jin]

hi, @FischerEric86

try this one:

SelectedPeriod = SELECTEDVALUE('DateTable'[YearMonth])

Period_Back1 = SELECTEDVALUE('DateTable'[YearMonth]) - 1
Period_Back2 = SELECTEDVALUE('DateTable'[YearMonth]) - 2
Period_Back3 = SELECTEDVALUE('DateTable'[YearMonth]) - 3

Period_Forward1 = SELECTEDVALUE('DateTable'[YearMonth]) + 1
Period_Forward2 = SELECTEDVALUE('DateTable'[YearMonth]) + 2
Period_Forward3 = SELECTEDVALUE('DateTable'[YearMonth]) + 3

InitialQuantity =
CALCULATE(
SUM(Sales[Quantity]),
'DateTable'[YearMonth] IN { [Period_Back1], [Period_Back2], [Period_Back3] }
)

NewQuantity =
CALCULATE(
SUM(Sales[Quantity]),
'DateTable'[YearMonth] IN { [Period_Forward1], [Period_Forward2], [Period_Forward3] }
)

InitialPrice =
CALCULATE(
AVERAGE(Sales[Price]),
'DateTable'[YearMonth] IN { [Period_Back1], [Period_Back2], [Period_Back3] }
)

NewPrice =
CALCULATE(
AVERAGE(Sales[Price]),
'DateTable'[YearMonth] IN { [Period_Forward1], [Period_Forward2], [Period_Forward3] }
)

PriceElasticity =
DIVIDE(
(([NewQuantity] - [InitialQuantity]) / ([NewQuantity] + [InitialQuantity]) / 2),
(([NewPrice] - [InitialPrice]) / ([NewPrice] + [InitialPrice]) / 2)
)

[tamerj1]

Hi @FischerEric86 
Please refer to attached sample file with the proposed solution

Month Elastic = 
VAR Months = 3
VAR Result = 
    GENERATE ( 
        SUMMARIZE ( 
            'Date',
            'Date'[Year],
            'Date'[Month],
            'Date'[Year Month],
            'Date'[Year Month Key]
        ),
        VAR CurrentYearMonthKey = 'Date'[Year Month Key]
        VAR YearMonthKeys = VALUES ( 'Date'[Year Month Key] )
        VAR MonthsBefore = 
            TOPN ( 
                Months + 1, 
                FILTER ( YearMonthKeys, 'Date'[Year Month Key] <= CurrentYearMonthKey ),
                'Date'[Year Month Key]
            )
        VAR MonthsAfter = 
            TOPN ( 
                Months, 
                FILTER ( YearMonthKeys, 'Date'[Year Month Key] > CurrentYearMonthKey ),
                'Date'[Year Month Key],
                ASC
            )
        VAR Result =
            SELECTCOLUMNS ( 
                UNION ( MonthsBefore, MonthsAfter ),
                "Year Month Key Elastic", 'Date'[Year Month Key]
            )
        RETURN
            Result
    )
RETURN
    Result

[FischerEric86]

Thank you for the dax script.  This solved one portion of the project I didn't think.  See example below as I should have provided the elastcity calculation and provided my context. 

What I wanted was a way to select a period, same example as above 202402 that would then include 202403 and 202404, three months, which would be identified as Quantity New (QN) and Price New (PN) as seen below in the example.  And then it would know to look back three periods, in this example 202401, 202312, 202311 which would then be Initial Quanity and Initial Price.  

This way whenver we would select a period it would calculate price elasticity using three periods forward vs the prior three periods.

Let me know if this makes sense. 

If you sell 10,000 reams of paper at $100 per ream and then raise the price to $150 per ream and sell 7,000 reams, your elasticity of demand would be -0.88. This would be considered inelastic because it is less than one.

Broken down even further to include the calculation of percent change, this formula looks like:

((QN - QI) / (QN + QI) / 2) / ((PN - PI) / (PN + PI) / 2)

• QN = New quantity (7,000)
• QI = Initial quantity (10,000)
• PN = New price ($150)
• PI = Initial price ($100)

Our numbers plugged into this formula would be:

(7,000 - 10,000) / (7,000 +10,000) /2) / (150 - 100) / (150 - 100) / 2)