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
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:
Calculating the current fiscal year, I'm using the following DAX:
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
Solved! Go to Solution.
@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.
@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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |