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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
rbowen
Helper I
Helper I

Previous Fiscal Year YTD Sales Calculation Not Working

I'm creating a basic executive dashboard showing total sales by fiscal year for FY22 and FY23 for numerous department categories. Our fiscal year runs from September 1st to August 31st. The model is pretty straightforward, just a sales table containing dates and amounts, and a calendar table with all the necessary columns (dates, fiscal months, month numbers, etc. I have a single cross filter relationship between the calendar and sales tables:

rbowen_0-1687813104068.png

Calculating the current fiscal year, I'm using the following DAX: 

YTDSales2 = totalytd([TotalSales2], 'Calendar'[Date], ALL('Calendar'), "08/31")
 
This gives me the correct sales data for the various departments from 9/1/2022 thru today (June 26th). I need FY22 sales data for the same categories for the same period in FY22, 9/1/2021 thru 6/26/2022. Figuring this should be a simple SAMEPERIODLASTYEAR function, I'm using this DAX for the FY22 YTD calculation for fical year 22:

CALCULATE([YTDSales2],SAMEPERIODLASTYEAR('Calendar'[Date]))
 
Instead of getting the sales data for the correct YTD range, I'm getting all of FY22 sales data for each category. 
rbowen_1-1687813464777.png

 

Using category FUEL as an example, what I should see for FY22 Sales is $84,507,646 but am getting $109,780,654 - the total FUEL sales for all of FY22 (9/1/2021 - 8/31/22). I've tried a variety of different ways of calculating FY22 sales for comparing to the same period of FY23 but all of them give the incorrect results. I've used these formulas in the past and they've worked but, for some reason, I can't get them to work now. Any ideas on what I'm missing?

 

Thank you

 
 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@rbowen , Try like

YTD Sales = CALCULATE([TotalSales2],DATESYTD('Calendar'[Date],"08/31"))
Last YTD Sales = CALCULATE([TotalSales2],DATESYTD(dateadd('Calendar'[Date],-1,Year),"08/31"))
This year Sales = CALCULATE([TotalSales2],DATESYTD(ENDOFYEAR('Calendar'[Date]),"08/31"))
Last year Sales = CALCULATE([TotalSales2],DATESYTD(ENDOFYEAR(dateadd('Calendar'[Date],-1,Year)),"08/31"))

 

YTD =
var _max = if(isfiltered('Calendar'),MAX( 'Calendar'[Date]) , today())
var _min = if(month(_max) <9, Date(year(_max)-1,9,1) ,Date(year(_max),9,1) )
return
calculate([net],DATESBETWEEN('Calendar'[Date],_min,_max))

 

LYTD =
var _max1 = if(isfiltered('Calendar'),MAX( 'Calendar'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = if(month(_max) <9, Date(year(_max)-1,9,1) ,Date(year(_max),9,1) )
return
calculate([net],DATESBETWEEN('Calendar'[Date],_min,_max))

 

 

Make sure, date calendar table is marked as date table

 

Why Time Intelligence Fails - Powerbi 6 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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 :radacad sqlbi My Video Series Appreciate your Kudos.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@rbowen , Try like

YTD Sales = CALCULATE([TotalSales2],DATESYTD('Calendar'[Date],"08/31"))
Last YTD Sales = CALCULATE([TotalSales2],DATESYTD(dateadd('Calendar'[Date],-1,Year),"08/31"))
This year Sales = CALCULATE([TotalSales2],DATESYTD(ENDOFYEAR('Calendar'[Date]),"08/31"))
Last year Sales = CALCULATE([TotalSales2],DATESYTD(ENDOFYEAR(dateadd('Calendar'[Date],-1,Year)),"08/31"))

 

YTD =
var _max = if(isfiltered('Calendar'),MAX( 'Calendar'[Date]) , today())
var _min = if(month(_max) <9, Date(year(_max)-1,9,1) ,Date(year(_max),9,1) )
return
calculate([net],DATESBETWEEN('Calendar'[Date],_min,_max))

 

LYTD =
var _max1 = if(isfiltered('Calendar'),MAX( 'Calendar'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = if(month(_max) <9, Date(year(_max)-1,9,1) ,Date(year(_max),9,1) )
return
calculate([net],DATESBETWEEN('Calendar'[Date],_min,_max))

 

 

Make sure, date calendar table is marked as date table

 

Why Time Intelligence Fails - Powerbi 6 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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 :radacad sqlbi My Video Series Appreciate your Kudos.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Thank you Amit. The results of the measures you suggested:

 

Last YTD Sales = CALCULATE([TotalSales2],DATESYTD(dateadd('Calendar'[Date],-1,Year),"08/31"))

Still gives an FY22 sales value that is too high and appears to be calculating the full sales value for all of FY22 instead of the YTD value. For example, the FUEL category sales value for FY22 YTD (9/1/2021-6/27/2022) should be $84,969,999 but the measure above has it showing as $109,780,654. 

 

Last year Sales = CALCULATE([TotalSales2],DATESYTD(ENDOFYEAR(dateadd('Calendar'[Date],-1,Year)),"08/31"))

This measure gives a value that is much too low, only $48,918,547, and appears to be calculating the FUEL total from 9/1/2021 to 3/17/2022. 

 

LYTD =
var _max1 = if(isfiltered('Calendar'),MAX( 'Calendar'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = if(month(_max) <9, Date(year(_max)-1,9,1) ,Date(year(_max),9,1) )
return
calculate([net],DATESBETWEEN('Calendar'[Date],_min,_max))

This one calculates the correct value - with the [net] replaced with my [TotalSales2] measure. Seems like a bit of a convoluted way to calculate the previous fiscal YTD sales value but it works so who am I to complain. Thank you for your assistance.

rbowen_0-1687878199756.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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