Forum Discussion
Performance compared to same period last year
Resolver IVHi MrWolle
please post the code of your DAX measures and maybe a screenshot of your dashboard and data model so we can advise.
PS: we're all friendly here ;-)
Frank
Hi BetterCallFrank,
Thanks for your reply.
I will try to explain, might get a little complicated though.
My data period covers from 2015 to YTD, i have the following tables
Date table covering from 2015 to 2025
Delivery table were volume per delivery no. is used (table include other data)
Shipment table showing shipment no. and included delivery numbers + loading date.
Distinct count on shipments, shipment type 76 is excluded since these are combined shipments
Distinct shipments = calculate(DISTINCTCOUNT(Shipments[Shipment]); Shipments[ST] <> 76)
Summarize of CDM from deliveries and convert it to M3
M3 calc = CALCULATE(SUM('Delivery data'[Volume corrected])/1000)
Utillization per month calc
Utillization per month = IFERROR('Delivery data'[M3 calc]/'Delivery data'[Distinct shipments];0)
Accumulated utilization per year
Utillization year = TOTALYTD([Utillization per month];'Date'[Date];DATESYTD(Shipments[ActShipEnd]))
The above is used for this graph
Next i calculate an accumulated year to date utililzation
Year to date = calculate('Delivery data'[Utillization per month]; DATESYTD('Date'[Date]))
And this is compared to target (40 m3) in this meassure
Variance YTD target = 'Delivery data'[Year to date]/40-1
Shown on this card on the Dashboard
Next meassure should be showing variance in utilization this year when compared to last year same period
Calculate last year to date performance
Same period last year m3 = Calculate([M3 calc];DATESBETWEEN('Date'[Date];"01-01-2016";DATEADD(LASTDATE(Shipments[ActShipEnd]);-1;YEAR)))
Same period last year shipments = Calculate([Shipments YTD];DATESBETWEEN('Date'[Date];"01-01-2016";DATEADD(LASTDATE(Shipments[ActShipEnd]);-1;YEAR)))
Shipments YTD = calculate(TOTALYTD([Distinct shipments];'Date'[Date]))
Same period last year = [Same period last year m3]/[Same period last year shipments]
Aaaand finaly the comparison meassure
Variance last year = 'Delivery data'[Year to date]/'Delivery data'[Same period last year]-1
Further more i have some slicers to select, shipping point, destination, truck type and transport planner, but when i use these the last measure last year vs. this year shows totally wrong numbers.
As you can see on the meassures i'm really a beginner, and googled my way to most of this. Which means it can probably be made much easier.
But i'm really struggeling to understand these time related DAX commands. Also tried with sameperiodlastyear, but couldn't get this to work.
/Jens
Hi MrWolle,
>>But i'm really struggeling to understand these time related DAX commands. Also tried with sameperiodlastyear, but couldn't get this to work.
These functions are used to filter with the specify date range, for example:
TotalYTD function =
Calculate(sum(Table[value]), Filter(Table, [Date]>=Date(current year,1,1) &&[Date]<=current Date))
If you want to know more about these time fucntions, you can refer to bleow links:
DAX basics in Power BI Desktop
Data Analysis Expressions (DAX) Reference
Regards,
Xiaoxin Sheng
Hi Anonymous
Thank you for your reply. I have studied the links you are refering to, and can see that i'd probably made my calculations more complex than neccesary. So i choose to rebuild my Dashboard, and creating new measures. But i still have a few questions.
But let me first try to explain background and data included:
The general intention with this dashboard, is to measure the average utilization per truck for current year compared to last year. For this i have 3 tables in Power Bi.
Calendar table with dates from 2015 to 2020
Shipment table
This is showing shipment no. (one shipment no. = one truck), delivery number(one delivery number = one delivery address), destination and shipment type and loading date.
Delivery table
This is showing delivery number, shipping point (plant this was shipped from), volume in CDM and weight in kg.
First step, create a line graph showing the average utillization per truck per month with an line for each year.
First measure is accumulated volume in CDM
Acc. volume = TOTALYTD(sum(Deliveries[Volume]);'Date'[Date])
Second measure is accumulated distinct count on shipments
Acc shipments = TOTALYTD(DISTINCTCOUNT(Shipment[Shipment]);'Date'[Date])
Finaly the utilization calculation, used for the line graph is (divided by 1000 used to convert CDM to to M3)
Utillization = [Acc. volume]/[Acc shipments]/1000
I hope that i am on the right track here? But i can say that the graph is working as expected and data showing up are correct. Only issue is that data for 2017 continues beyond the last day in loading day from shipment table, how do i avoid this?
Further i would like to show add 2 cards to dashboard, one showing YTD deviation from target (40m3) in % and another showing YTD deviation from same period last year in %.
The first should be quite simple (Utilization/target -1) but how would you suggest that i do last calculation?
/Jens
Hi MrWolle,
Sorry for slow response, you can refer to use below methods.
>>Only issue is that data for 2017 continues beyond the last day in loading day from shipment table, how do i avoid this?
I'd like to add a measure to get the max date of current year, then add a if statement to compare with the current date, if it greater than measure, return blank.
>>showing YTD deviation from target (40m3) in %
You only need to calculate the current year's total value, then use the target value divide the result value.
>>another showing YTD deviation from same period last year in %.
Modify the above formula to calculate based on current year -1.
Regards,
Xiaoxin Sheng