Create groups based on differences between two date columns HISTORICALLY

[langosh]

Hi Everyone,

I have an ORDER TABLE as my fact table which have the following sample data:

OrderID	OrderCreated	OrderClosed	DaysResolved	OutstandingDays
0001	10/07/2024	15/07/2024	5	0
0002	13/05/2024	16/05/2024	3	0
0003	20/07/2024	null		4

The days resolved shows how many days it took to close an order. 
The OutstandingDays shows how many days it is still open. 

NOW my problem is I need to create a visual (bar chart) which would show how many days it was opened on the previous days as well. For this I would like to use a calendar table which consists of all the days, weeks, etc... And somehow connect the two so I could make my final grouping based on how many days it WAS opened on that calendar date. 

E.g with orderID "0001": 

OrderID	OrderCreated	CalendarDate	OutstandingDays	Warning
0001	10/07/2024	10/07/2024	0	OK
0001	10/07/2024	11/07/2024	1	Needs Checking
0001	10/07/2024	12/07/2024	2	LATE

I wonder if you could help me out how should I approach this situation... Thank you in advance! 

[Anonymous · Accepted solution]

Hi, @langosh 

Based on your information, I create a sample table.

Then create a new calendar table:

Next, you need to expand the Orders table to include each date between and for each order. You can do this using Power Query. 

Add a custom column with the following formula to create a list of dates

= let 
a = List.Dates([OrderCreated], Duration.Days([OrderClosed] - [OrderCreated]) + 1, #duration(1, 0, 0, 0)) 
in if [OrderClosed] = null then null else a 

Expand this new column to create a row for each date

Rename the new column to CalendarDate.

Add a calculated column to determine the number of outstanding days for each date

 OutstandingDays = DATEDIFF('Order'[OrderCreated], 'Order'[CalendarDate], DAY) 

Add another calculated colunm:

Warning = 
SWITCH(
    TRUE(),
    'Order'[OutstandingDays] = 0, "OK",
    'Order'[OutstandingDays] = 1, "Needs Checking",
    'Order'[OutstandingDays] >= 2, "LATE"
    
)

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[langosh]

Yes Thank you! 

This is very similar how I solved it in the meantime. 

thank you!

[langosh]

Hi,

I'd like to make a historical stacked barchart with the data of all OrderIDs. Divided into Weeks. 

OrderID	OrderCreated	CalendarDate	OutstandingDays	Warning
0001	10/07/2024	10/07/2024	0	OK
0001	10/07/2024	11/07/2024	1	Needs Checking
0001	10/07/2024	12/07/2024	2	LATE

[ryan_mayu]

sry, still not understand the request. 

Is this table the expected output?What do you mean by devided into weeks?

[ryan_mayu]

So you have three tables in your model? what's the expected output?