Get Market Share from previous period

[Anonymous]

Hi, I have a pivot table with my products(columns) and some periods(rows), its market share for this month and I want another column with market share(values) but from same period of last year. I tried many things but no way.

My market share function is:

MS = [Period Selected]/CALCULATE([Period Selected],ALLSELECTED('m_product (2)'))*100

and I tried another DAX where THIS IS FAILING

MS LY = [Previous Period Selected]/CALCULATE([Previous Period Selected],ALLSELECTED('m_product (2)'))*100

another formulas:

Period Selected = SWITCH(TRUE(),

VALUES(m_period[Period])="MTH",[MTH],

VALUES(m_period[Period])="QRT",[QRT],

VALUES(m_period[Period])="YTD",[YTD],

VALUES(m_period[Period])="MAT",[MAT],

BLANK()

)

Thanks!!

[sirlanceohlott]

I'll second @Icey's solution. This would be the easiest way to get to your required goal of showing the previous period market share. 

[Icey]

Hi @Anonymous ,

How about this?

Create a Dates table and create relationship between your fact table and Dates table. Then, create [MS LY] measure like so:

MS LY =
CALCULATE (
    [MS],
    FILTER ( ALL ( Dates ), Dates[Year] = MAX ( Dates[Year] ) - 1 ),
    SAMEPERIODLASTYEAR ( Dates[Date] )
)

If this does not work, please share me some sample data for test.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey ,

I created the 'Dates' table.

This is what I want to see:

And this are my formulas:

MS = [Period Selected]/CALCULATE([Period Selected],ALLSELECTED('m_product_ims (2)'))*100

MS LY = CALCULATE([MS],FILTER(ALL('Date'),'Date'[Year]=MAX('Date'[Year])-1),SAMEPERIODLASTYEAR('Date'[Date]))

MTH = if(SELECTEDVALUE(m_period[PeriodValue])=1 || ISBLANK(SELECTEDVALUE(m_period[PeriodValue])),

CALCULATE(SUM(FFFT_ARG_DDD_066E_202004[Valor]),DATESINPERIOD(m_mes[Period].[Date],LASTDATE(m_mes[Period]),-1,MONTH)))

MTH LY = CALCULATE([MTH],PARALLELPERIOD(m_mes[Period].[Date],-20,MONTH))

Period Selected = SWITCH(TRUE(),

VALUES(m_period[Period])="MTH",[MTH],

VALUES(m_period[Period])="QRT",[QRT],

VALUES(m_period[Period])="YTD",[YTD],

VALUES(m_period[Period])="MAT",[MAT],

BLANK())

Thanks!

[Icey · Accepted solution]

Hi @Anonymous ,

Please check my example attached.

Use "Year" column from Dates table not financials table in my example.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @Icey , that works!