Would like help to monitor time of exported packages

[DeBIe]

Hello all,

I'm a beginner when it comes to Power BI and I would like to monitor packages from the moment they are picked up and exported. I would like to be able to create a dashboard with tiles that show the following numbers/details:

• Latest(realtime) exported package time today(in hours/minutes)
• Slowest exported package today (in hours/minutes)
• Fastest exported package today (in hours/minutes)
• Average exported package time current month
• Average exported package time last month

I also would like to display a stacked bar chart for today, yesterday, last week, and last month which displays the average export time. In addition, I would like to add a line to the graph with the slowest/fastest return and the maximum time a package may take to process (I need to be able to fill in a maximum time which will be displayed in a red line or so). I think I will be able to do this if I have the right measures.

The dataset that I have available looks like this:

ID	CompanyName	PackageNumber	StartDateTime	ExportDateTime
1	Package Express	Package1	2020-08-05 10:00:00	2020-08-05 11:00:00
2	Package Express	Package2	2020-08-05 11:00:00	2020-08-05 11:30:00

Can anyone help me get started creating this?
Thank you very much for your time. All help is much appreciated.

Kind Regards

[v-diye-msft]

Hi @DeBIe 

Kindly find my below results:

Pbix attached.

[DeBIe]

@v-diye-msft thank you for your reply. I have downloaded the pbix file but I can't open it. I get an error saying:

"Unable to open document. Incompatible with your current version of Microsoft Power BI Desktop. Please Install the latest version and try opening the document again."

I have PBI desktop installed via the Microsoft Store, so it should stay up to date automatically. I have tried to install the latest version manually but I still get the same error. Any thoughts on how to fix this? 

[v-diye-msft · Accepted solution]

Hi @DeBIe 

My desktop version is Version: 2.84.461.0 64-bit (August 2020), what about yours? would you mind trying to check the latest MSI version?

I put my measures here for your ease:

Latest exported package time today = CALCULATE(MAX('Table'[ExportDateTime]),FILTER('Table',DATEDIFF([ExportDateTime],TODAY(),DAY)=0))

Slowest exported package today = var a = CALCULATE(MAX([Duration]),FILTER('Table',DATEDIFF([ExportDateTime],TODAY(),DAY)=0))
return
CALCULATE(MAX([ExportDateTime]),FILTER('Table',[Duration]=a))

fastest exported package today = var a = CALCULATE(MIN([Duration]),FILTER('Table',DATEDIFF([ExportDateTime],TODAY(),DAY)=0))
return
CALCULATE(MAX([ExportDateTime]),FILTER('Table',[Duration]=a)) 

Average exported package time current month = CALCULATE(AVERAGE('Table'[Duration]),FILTER('Table',DATEDIFF('Table'[ExportDateTime],TODAY(),MONTH)=0))

Average exported package time last month = CALCULATE(AVERAGE('Table'[Duration]),FILTER('Table',DATEDIFF('Table'[ExportDateTime],TODAY(),MONTH)=1))

[DeBIe]

It seems that I have the July 2020 version (2.83). I don't know why it has not updated automatically because I tried to prevent having an older version by installing it via the Microsoft Store. Then it would update automatically. I tried downloading PBI Desktop thru Microsoft website but it gives me July 2020 version again.

Thanks for posting the measures. I'm going to try them now. 

[amitchandak]

@DeBIe , You have time like

diff in time = [ExportDateTime] - [StartDateTime]

diff in hours = datediff([StartDateTime],[ExportDateTime],Minute)/60.0

Now you can have Top1, Bottom N, Rank, Average on diff in hours

Work with duration -[diff in time ]

diff in hours :https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...

top example

Rank Top 10= CALCULATE(if([City Rank]<=10,[Sales],BLANK()) ,VALUES(Geography[City]))	
	Rank Top 10 1 = sumx(VALUES(Geography[City]),if([City Rank]<=10,[Sales],BLANK()) )
	Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))
	

use asc for bottom

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...