Performance compared to same period last year

Question

Hello all,&nbsp;I'm new to Power BI, actually new to DAX in general. I'm trying to create a Dashboard to track the utilization of trucks. For this i have designed an line chart showing the avg. utillization on years, per month per truck.&nbsp;Beside this i have included a card showing YTD deviation from target, and a card showing YTD compared to same period last year. It all works until i beging to use the slicers that i have included in the Dashboard, then the YTD compared to last year wont work as figures showing up does not match actual figures.&nbsp;Is here a friendly person to whom i could send my Dashboard, who would spend some time checking this?&nbsp;If this it not allowed let me know, and i will try my best to describe it here instead.&nbsp;As i said, i'm new to this so probably made some mistakes.&nbsp;Thanks.&nbsp;/Jens

bettercallfrank · Answer

Hi&nbsp;MrWolleplease 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

mrwolle · Answer

Hi BetterCallFrank,&nbsp;Thanks for your reply.&nbsp;I will try to explain, might get a little complicated though.&nbsp;My data period covers from 2015 to YTD, i have&nbsp;the following tablesDate table covering from 2015 to 2025Delivery table&nbsp;were volume per delivery no.&nbsp;is used&nbsp;(table include other data)Shipment table showing shipment no.&nbsp;and included delivery numbers + loading date.&nbsp;Distinct count on shipments, shipment type 76 is excluded since these are combined shipmentsDistinct shipments = calculate(DISTINCTCOUNT(Shipments[Shipment]); Shipments[ST] &lt;&gt; 76)&nbsp;Summarize of CDM from deliveries and convert it to M3M3 calc = CALCULATE(SUM('Delivery data'[Volume corrected])/1000)&nbsp;Utillization per month calcUtillization per month = IFERROR('Delivery data'[M3 calc]/'Delivery data'[Distinct shipments];0)&nbsp;Accumulated utilization per yearUtillization year = TOTALYTD([Utillization per month];'Date'[Date];DATESYTD(Shipments[ActShipEnd]))&nbsp;The above is used for this graphNext i calculate an accumulated year to date utililzationYear to date = calculate('Delivery data'[Utillization per month]; DATESYTD('Date'[Date]))&nbsp;And this is compared to target (40 m3) in this meassureVariance YTD target = 'Delivery data'[Year to date]/40-1&nbsp;Shown on this card on the Dashboard&nbsp;Next meassure should be showing variance&nbsp; in utilization this year when compared to last year same period&nbsp;Calculate last year to date performanceSame period last year m3 = Calculate([M3 calc];DATESBETWEEN('Date'[Date];"01-01-2016";DATEADD(LASTDATE(Shipments[ActShipEnd]);-1;YEAR)))&nbsp;Same period last year shipments = Calculate([Shipments YTD];DATESBETWEEN('Date'[Date];"01-01-2016";DATEADD(LASTDATE(Shipments[ActShipEnd]);-1;YEAR)))&nbsp;Shipments YTD = calculate(TOTALYTD([Distinct shipments];'Date'[Date]))&nbsp;Same period last year = [Same period last year m3]/[Same period last year shipments]&nbsp;Aaaand finaly the comparison meassureVariance last year = 'Delivery data'[Year to date]/'Delivery data'[Same period last year]-1&nbsp;&nbsp;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.&nbsp;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.&nbsp;But i'm really struggeling to understand these time related DAX commands. Also tried with sameperiodlastyear, but couldn't get this to work.&nbsp;/Jens

anonymous · Answer

Hi MrWolle,&nbsp;&gt;&gt;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]&gt;=Date(current year,1,1) &amp;&amp;[Date]&lt;=current Date))&nbsp;If you want to know more about these time fucntions, you can refer to bleow links:DAX basics in Power BI DesktopData Analysis Expressions (DAX) Reference&nbsp;Regards,Xiaoxin Sheng

mrwolle · Answer

Hi Anonymous&nbsp;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.&nbsp;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&nbsp;3 tables in Power Bi.&nbsp;Calendar table with dates from 2015 to 2020&nbsp;Shipment tableThis is showing shipment no. (one shipment no. = one truck), delivery number(one delivery number = one delivery address), destination and shipment type and loading date.&nbsp;Delivery tableThis is showing delivery number, shipping point (plant this was shipped from), volume in CDM and weight in kg.&nbsp;First step, create a line graph showing the average utillization per truck per month with an line for each year.&nbsp;First&nbsp;measure&nbsp;is accumulated volume in CDMAcc. volume = TOTALYTD(sum(Deliveries[Volume]);'Date'[Date])&nbsp;Second measure is accumulated distinct count on shipmentsAcc shipments = TOTALYTD(DISTINCTCOUNT(Shipment[Shipment]);'Date'[Date])&nbsp;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&nbsp;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&nbsp;is that&nbsp;data for 2017&nbsp;continues beyond the last day in loading day from shipment table, how do i avoid this?&nbsp;&nbsp;Further&nbsp;i would like to show add 2 cards to dashboard, one showing YTD deviation from target (40m3) in %&nbsp;and another showing YTD deviation from same period last year in %.&nbsp;The first&nbsp;should be&nbsp;quite simple (Utilization/target -1)&nbsp;but how would you suggest that i do last calculation?&nbsp;/Jens&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi MrWolle,&nbsp;Sorry for slow response, you can&nbsp;refer to use below methods.&nbsp;&gt;&gt;Only issue&nbsp;is that&nbsp;data for 2017&nbsp;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.&nbsp;&gt;&gt;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.&nbsp;&gt;&gt;another showing YTD deviation from same period last year in %.Modify the above formula to calculate based on current year -1.&nbsp;Regards,Xiaoxin Sheng

Forum Discussion

Performance compared to same period last year

5 Replies

Related content

Comparing activity performance

Using Measure to Compare Current Period to Previous Period

Compare Against Previous Period KPI Card

New and lost Sales comparing periods

Compare data with previous period/last month