Forum Discussion

Frequent Visitor

4 years ago

YoY for calculation till selected month

Greetings everyone ! Here's the issue that I am facing. I have two tables, one for date and other for sales. Slicer is maintain for Month field; data need to be presented as below - 1. Yea...

Gabriel_Walkman

Icon for Continued Contributor rank

Continued Contributor

4 years ago

Hi there.

I recommend the MS learning paths, found here: https://docs.microsoft.com/en-us/learn/browse/?filter-products=BI&products=power-bi

Basically you'd create a measure for the sum, ie. sum = sum( [sales] ), and a measure for the ytd, ie. YTD = yeartodate( [sum], 'Calendar'[Date] ), and a measure for the change:
Var (%) =
var _prev = calculate( [YTD], sameperiodlastyear( 'Calendar'[Date] ))
return
divide( [YTD] - _prev, _prev )

Blue_BIGuy
Frequent Visitor
4 years ago
What you are suggesting does not work when there's a slicer on Month in the report. Kindly re-read the query, If I select a month in slicer then YTD should SUM up values only till selected month for each FY i.e. if I select March it should show Jan to March sale for FY21, FY20, FY19 and so on.
I have applied below formula for calculating YTD and its working fine and for YoY below formula is used which is not working -
MAX_MONTH = MAX('Date'[MonthNo])

Sales YTD =
Var A = [MAX_MONTH]
Return CALCULATE([SALES], ALL('Month'), 'Date'[MonthNo] <= A)

Sales YTD LY = CALCULATE([Sales YTD], SAMEPERIODLASTYEAR('Date'[Date]))
Sales YTD YoY = DIVIDE(Sales YTD-Sales YTD LY, Sales YTD LY, 0)

Kindly suggest if you know how can I correct YoY formula.
- Gabriel_Walkman
  Continued Contributor
  4 years ago
  Oh. Sorry, I've never used YOY myself so thought after quick googling that was the way. Just to make sure, you don't have a table named 'Month', right?
- manikumar34
  Solution Sage
  4 years ago
  MAX_MONTH = MAX('Date'[MonthNo])
  
  Sales YTD =
  Var A = [MAX_MONTH]
  Return CALCULATE([SALES], ALL('Month'), 'Date'[MonthNo] <= A)
  
  Sales YTD LY = CALCULATE([SALES], ALL('Month'), 'Date'[MonthNo] <= A && 'Date'[Year]=Year(Max(Date[Date]))
  Sales YTD YoY = DIVIDE(Sales YTD-Sales YTD LY, Sales YTD LY, 0)
  
  Just alter your LY to above DAX