YOY Growth starting from 100%

[Anonymous]

Hi Guys My team is preparing a Dashboard to demonstrate the YOY growth of the Net Fee Collected from different clients over the past 5 years. Since the dates are scattered in 3 different columns and cannot be filtered in one column we cannot use the time intelligence formulas. Therefore we have used the following revenue measures. 1. This Year NFC- TY NFC (In Lacs) = var MaxYear = MAX('Net Fee Collected'[Fiscal_Year]) //var year=[YTD] return CALCULATE(SUM('Net Fee Collected'[NFC YTD(In Lacs)]),FILTER('Net Fee Collected','Net Fee Collected'[Fiscal_Year] =MaxYear),FILTER('Net Fee Collected','Net Fee Collected'[Is CGP] = "Yes")) 2. Previous Year NFC- NFC_PY = var MaxYear = MAX('Net Fee Collected'[Fiscal_Year]) var PreviousFY = LEFT(MaxYear,4)-1 & "-" & RIGHT(MaxYear,4)-1 return CALCULATE([Total NFC],FILTER(ALLSELECTED('Net Fee Collected'),'Net Fee Collected'[Fiscal_Year] = PreviousFY)) 3. YOY growth %- YoY Growth % = DIVIDE(([TY NFC (In Lacs)]-[NFC_PY]),[TY NFC (In Lacs)]) YOY Growth % graph is automatically taking the stating year value as 100% growth. How can we make it 0%?

[Anonymous]

Hi there.

Can't you just bring the relevant dates into one (calculated) column and then connect to it a properly built Calendar? Then all your problems will be gone. Please try to make your life easier. DAX is simple only when the model is right. On the other hand, if the model is wrong, no amount of DAX will make it better and fast. Please keep in mind.

I suspect your model consists of one big table... You should refrain yourself from creating such models if you want to have an easy time writing DAX, understanding it and not introducing bugs you won't even be able to find. One-table models should never be created. Please use the dimensional modeling principles to stay away from troubles.

Best

D

[amitchandak]

@Anonymous , As far As I know in YOY prior should be below

YOY growth %- YoY Growth % = DIVIDE(([TY NFC (In Lacs)]-[NFC_PY]),[NFC_PY])

Also for YTD/YOY You refer these

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))


YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)


YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.