Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MarcUrdang
Post Patron
Post Patron

comparing data by dates

Good morning .. I will try and describe my challenge as best as possible ..hope it will make sense. 

 

1. I have two dates from which I derive a DATEDIFF per category called the Lead Time

2. There are many rows in the dataset each with its own Lead Time over a particular period of time

3. These Lead Times are based on various categories such as Port of Departure and Date of Departure

4. I have also created a DATE TABLE linking the DATE in the Date Table to the requried date in the actual data

 

5. So as an example:  From the port of Shanghai the Lead Time is 4 days for Nov 2019 (and various other Lead Times for lots of other dates in 2019) but I would use a date filter and choose a month in 2019) .. so would then choose Nov 2019 showing the 4 days as stated (or a combination of dates in 2019 showing the median of the then calculated Lead Time).

 

6. I would then like to see the variance (days and %) of the Lead Times, based on that Port of Departure in each month of 2020. So if the Lead Time in Jan 2020 from Shanghai is 10 days and the Lead Time from Shanghai in the chosen range in 2019, as in point 5, was 4 then the variance would be 6 and the % would be a positive 150%

 

7. I would like to see Point 6 for each of the months in 2020 as the year progresses .. so while the month of February 2020 progresses I would like to see that % change as the data is refreshed daily

 

8. I would then like t show this on a map where the circle would be green if it was a negative % and red if it was a positive %.

 

I hope I have explained this clearly.

thanks

Marc

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@MarcUrdang ,

Refer if this article and file can help: https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

@MarcUrdang 

Sure

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

thanks Amit .. I have just mailed you the files

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@MarcUrdang ,

Refer if this article and file can help: https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

thank you for your response ... however as a new user this is way to technical for me.... is there any way I can send you my workbook for easy clarification?

thanks

Marc

@MarcUrdang 

Sure

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

thanks Amit .. I have just mailed you the files

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.