Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Blue_BIGuy
Frequent Visitor

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.  Year to date sales based on selected month. i.e. if I have selected June then YTD sum sales till June for each FY. 

 2. Year over Year, whatever values we have achieved in 1st column needs to compare it with its previous year.  i.e. For 2021 = (YTD of 2021 till June -  YTD of 2020 till June)/YTD of 2020 till June. 

Blue_BIGuy_0-1637568414971.png

 

 

Any ideas how it can be achieve

 

4 REPLIES 4
Gabriel_Walkman
Continued Contributor
Continued Contributor

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 )

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. 

 

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




If this helps, Appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




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?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.