Forum Discussion
YoY % Change conditional to custom years
Hello,
I am trying to create a measure for YoY % Change with some conditional rules and comparison against specific periods. I have a dataset collecting the monthly footfall count for 12 venues from 2016. I would like to show a YoY % Change from previous year until 2020; then, starting from 2021 (so data for 2021 and 2022), I would like to compare to 2019 (the last "normal" year) instead of comparing with the previous year.
To do so I believe I have to create a conditional rule to run the regular YoY for all dates pre 2021 and, for dates past 2021, a custom formula for YoY always against 2019. How can I do so?
Thank you in advance,
Omar
Anonymous , Last year's formula
example using date table
Year behind Sales =
var _year = 2019
var _diff = 2019- year(max('Date'[Date]))
return
if(year(max('Date'[Date])) <= 2019 ,
CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year)) ,
CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],_diff ,Year)) )
Hi Anonymous
It would be better if you could provide some sample data. Assume your data is like below, you can add a Year column to it. (* My sample data starts from 2017 and it uses month start date in Month column.)
Then create these measures.
YoY % Change = VAR _thisYearCount = SUM('Table'[Footfall Count]) VAR _previousYearCount = CALCULATE(SUM('Table'[Footfall Count]),ALL('Table'[Year]),'Table'[Year]=MAX('Table'[Year])-1) RETURN DIVIDE(_thisYearCount-_previousYearCount,_previousYearCount)YoY % by 2019 = VAR _thisYearCount = SUM('Table'[Footfall Count]) VAR _2019Count = CALCULATE(SUM('Table'[Footfall Count]),ALL('Table'[Year]),'Table'[Year]=2019) RETURN DIVIDE(_thisYearCount-_2019Count,_2019Count)YoY % = VAR _year = MAX('Table'[Year]) RETURN IF(_year<2021,[YoY % Change],[YoY % by 2019])If it doesn't meet your requirement, please share some sample data and expected output to help us understand it better.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
3 Replies
- amitchandak
Super User
Anonymous , Last year's formula
example using date table
Year behind Sales =
var _year = 2019
var _diff = 2019- year(max('Date'[Date]))
return
if(year(max('Date'[Date])) <= 2019 ,
CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year)) ,
CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],_diff ,Year)) )
v-jingzhangCommunity Support
Hi Anonymous
It would be better if you could provide some sample data. Assume your data is like below, you can add a Year column to it. (* My sample data starts from 2017 and it uses month start date in Month column.)
Then create these measures.
YoY % Change = VAR _thisYearCount = SUM('Table'[Footfall Count]) VAR _previousYearCount = CALCULATE(SUM('Table'[Footfall Count]),ALL('Table'[Year]),'Table'[Year]=MAX('Table'[Year])-1) RETURN DIVIDE(_thisYearCount-_previousYearCount,_previousYearCount)YoY % by 2019 = VAR _thisYearCount = SUM('Table'[Footfall Count]) VAR _2019Count = CALCULATE(SUM('Table'[Footfall Count]),ALL('Table'[Year]),'Table'[Year]=2019) RETURN DIVIDE(_thisYearCount-_2019Count,_2019Count)YoY % = VAR _year = MAX('Table'[Year]) RETURN IF(_year<2021,[YoY % Change],[YoY % by 2019])If it doesn't meet your requirement, please share some sample data and expected output to help us understand it better.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Thank you bot v-jingzhang and amitchandak ,
I have managed to find a solution by combining your two answers.
I ended up needing to just create the 3 below measures (as I already had a model for my dates):
YoY % by previous year =
VAR _thisYear = sum('Footfall UK'[Footfall Count])
VAR _previousYear = calculate(sum('Footfall UK'[Footfall Count]),dateadd('Date'[Date].[Date],-1,YEAR))
RETURN
DIVIDE(_thisYear-_previousYear,_previousYear)YoY % by 2019 =
VAR _thisYear = sum('Footfall UK'[Footfall Count])
VAR _2019Count = calculate(sum('Footfall UK'[Footfall Count]),dateadd('Date'[Date].[Date],2019-year(max('Date'[Date])),year))
RETURN
DIVIDE(_thisYear-_2019Count,_2019Count)YoY % Change =
VAR _year=max('Date'[Date].[Year])
RETURN
IF(_year<2021,[YoY % by previous year],[YoY % by 2019])Thank you both!
Omar