Time Aggregation Question

[TonyO]

I'm contributing a question to a popular topic: DATEDIFF magic! 

I’m working on a problem that is a slight variation to the usual “datediff” issue. I have a table in Power BI (sample data attached) from a supply chain report I maintain that tells me in a weekly report (the Report Week field) the date (Delivery Date) that a Global Region will be installing servers for the next 24 months (each weekly report can have up to 24 dates for each region, and these dates change). A region could change their delivery date weekly, and this change, the delta between the Delivery Date in one weekly report and any other weekly report date, is what I need to calculate.

An example:

The Weekly Report on 5/25 has Dublin with Delivery Dates of 4/1/20,5/1/19,2/1/19,10/10/18

The Weekly Report on 6/1 has Dublin with Delivery Dates of 3/1/20,4/16/19,10/10/18,11/12/18

A calculation needs to tell me the delta in Delivery Dates between those two Report Dates (though I could want to see Dublin’s delta for january 10th and october 15th, or all dates in between).

The number of Delivery Dates for a region changes, and the Delivery Dates themselves change often.

The functionality I need:  Report Date is in a slicer/other PBI element (there are about 10 report dates currently, new one every week, natch). Users will pick any Report Date from the slicer/whatever works, and in a card/any visual that works see the cumulative delta between all the Delivery Dates in that range.

The business case for this is that we are reporting the changes to Delivery Date that our customers have made, and tracking those changes over time.

Any help finding a solution is greatly appreciated.

-T

Edit: Formatting

 Link to sample data: Sample Data

[v-lili6-msft]

hi, @TonyO

I'm a little confused about your description,

An example:

The Weekly Report on 5/25 has Dublin with Delivery Dates of 4/1/20,5/1/19,2/1/19,10/10/18

The Weekly Report on 6/1 has Dublin with Delivery Dates of 3/1/20,4/16/19,10/10/18,11/12/18

there are no delivery date of 4/1/20,5/1/19,2/1/19,10/10/18 in Report week5/25 of Dublin.

And could you tell me that when selected Report week is 5/25 and region is Dublin, what is the result?

could tell us several expected outputs on this sample data?

Best Regards,

Lin

[TonyO]

Thanks for responding, I apologize for my delayed reply. 

I apologize for using those Delivery Dates for Dublin, they were only as an example and don't match what is in the sample data. They were meant to illustrate that the Delivery Dates can completely changed from one Report Week to the next. 

Sample Output This is a mockup of what the final output will look like. The table in the middle has details on the data from each week's Report, and the Delivery Date for that week. Next to this week's Delivery Date is last week's delivery date, and next to that is a calculation of how many days the Delivery Date changed from last week's Report. Users will choose the date they want to see the Delivery Date delta between - these dates will appear in the cards on the left, as Start Date and End Date. To the right of the table is a summary of cumulative date changes for the selected period (and the change in MW).