Forum Discussion

MrWolle's avatar
MrWolle
Frequent Visitor
9 years ago

Performance compared to same period last year

Hello all,   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. utilli...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

MrWolle's avatar
MrWolle
Frequent Visitor
9 years ago

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