Scaffold Date in Power Bi

[Angelvan]

I posted a similar questin before but now I now the solution is to create a scaffolded data set but still don't know how to do it in Power Bi.

Can anyone help with the query below:

I want to be create a graph that shows open or active cases in a point of time.

I two tables: Cases and Master Date.

Cases have the following important fields:

Opened date – the date that a case was opened

Close date – the date that a case was closed

Master Date table has dates from 1/1/2019 until 31/12/2025. It is like a reference table.

This table is not related to my main table in any way.

I want to create a new column to define the relationship between the two and in the process create a new column which will give me many rows of data for each row to tell me the Active Date

e.g.

Case Ref	Open Date	Close Date	Active Date
A	12/03/2019	16/03/2019	12/03/2019
			13/03/2019
			14/03/2019
			15/03/2019
			16/03/2019
B	12/03/2019	15/03/2019	12/03/2019
			13/03/2019
			14/03/2019
			15/03/2019
C	13/03/2019	15/03/2019	13/03/2019
			14/03/2019
			15/03/2019

I expect the expression is something like this

IF

Master Date [Date] >= Cases[Open On]

AND

Master Date [Date] <= Cases[Close Date] (And if Close Date is Null make it Today)

THEN

SET Active Date to Master Date??

[HotChilli · Accepted solution]

Have a look at this

https://community.powerbi.com/t5/Desktop/Calculate-DayNumber-between-Two-Dates/m-p/773693#M372773

There's a DAX solution, a Power Query solution and a pbix to look at.

It's a slightly different case to yours but you should get all the info you need

[Angelvan]

Thanks very much - this worked!