Automate a Year over Year Calculation

[Anonymous]

I want to calculate YoY growth

Here is my dax using a manual input of 40 for the fiscal week:

Sales Growth % (YoY)  = Calculate ([Total Sales], 'Sales'[Fiscal_Year]= 2020)/ Calculate ( [Total Sales], 'Sales'[Fiscal_Year]= 2019, filter('Sales', 'Sales'[Fiscal_Week]<= 40))-1

This dax works just fine and gives me -10%.  I just need to add automation.  So I use a max function and determine max fiscal week = 40:

Max Fiscal Week = Calculate(max('Sales'[Fiscal_Week]),'Sales'[Fiscal_Year]=2020)

That works just fine as well and returns a value of 40 (not text).  Now, I want to replace the 40 with Max Fiscal Week:

Sales Growth % (YoY) Test = Calculate ([Total Sales],'Sales'[Fiscal_Year]= 2020)/ Calculate ( [Total Sales], 'Sales'[Fiscal_Year]= 2019, filter('Sales', 'Sales'[Fiscal_Week]<= [Max Fiscal Week]))-1

This dax yields “Infinity”.  

[v-easonf-msft]

Hi,  @Anonymous 

Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Anonymous , In case you have date you can use time intelligence with date table

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 year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year 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))

This will work with year table too

//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))

In case you do not have date to create a week and year table(separate) and use that

For week refer

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

YTD : https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

[Greg_Deckler · Accepted solution]

@Anonymous You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.