YOY & MOM for specific year with slicer.

[bideveloper555]

hi,

this is my YOY measure for current year - 3 which is 2019.

but if we go into 2022, i still want to show 2019 .

TurnoverYo3Y =

        CALCULATE(

            SUM('tablename'[Amount]),

            DATEADD('Date'[Date].[Date], -3, YEAR)

        )

how do i replace with static value as 2019.

 Same for 3 MOM% 3 year back vs current but again 3 years back will 2019 always.

Amt 3Y 3 MoM% =

VAR __PREV_MONTH =
CALCULATE(
SUM('Tablename'[Amt]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-36,MONTH)),-3,MONTH)

)

var __CURRENTMONTH = CALCULATE(SUM('Tablename'[Amt]),DATESINPERIOD('Date'[Date],ENDOFMONTH('Date'[Date]),-3,MONTH))
Var _SalesYOY = IF (
NOT ISBLANK ( __CURRENTMONTH )
&& NOT ISBLANK ( __PREV_MONTH ),
__CURRENTMONTH - __PREV_MONTH
)

RETURN
DIVIDE(
_SalesYOY,
__PREV_MONTH
)


)

 @amitchandak 

[bideveloper555]

thank you amit for your quick feed back.

power bi model as date table and every thing work fine for yoy and yoy last 2yrs or 3 yrs.

but i want to have yoy , current user selection vs 2019 always.

if user select from date slicer jan 2021, i need to show jan 2019 yoy.

if user selects jan 2020, i need to show jan 2020 vs jan 2019

if user select feb 2021,than feb 2021 vs feb 2019.

2019 will be static year

as going forward, ser selects jan 2022, than yoy should be jan 2019 vs jan 2022.

I did read the medium article but there is no information about static year selection with dynamic month change.

2019 WILL BE STATIC YEAR.

 sorry to be pain.

@amitchandak 

[amitchandak]

@bideveloper555 ,Mark date table as date table , option on right click. do not use .date in time intelligence

This should work with any period , mean if you group by month this will give month data

CALCULATE(
SUM('tablename'[Amount]),
DATEADD('Date'[Date], -3, YEAR)

)

Only Month or MTD

CALCULATE(
SUM('tablename'[Amount]),
datesmtd(DATEADD('Date'[Date]., -3, YEAR) )

)

for YOY or YTD 3 year back

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-3,Year),"12/31"))
This year= CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year= CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-3,Year)),"12/31"))

3 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-3,Year))
3 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR(dateadd('Date'[Date],-2,Year)))

example

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])