Create a table with time series between two dates FOR EACH project in project table

[Anonymous]

Hi,

I trying to establish a time series data set to serve as input for a line chart in DAX. 

Below is a sample Projects table of my starting point.

ProjectID	TimeRegistrationDateMin	StatusReportDateMax
1	01-01-2020	05-01-2020
2	10-01-2020	13-01-2020
3	20-01-2020	27-01-2020

To ensure all dates between the [TimeRegistrationDateMin] and [StatusReportDateMax] are available as row entries in the to-be table, I have tried using the GENERATESERIES() function. However, I cannot get my head around how to loop over each project of the Projects table, to produce a time series for each project in the same output table.

Desired output is as follows: 

ProjectID	Date
1	01.10.2020
1	02.10.2020
1	03.10.2020
1	04-01-2020
1	05-01-2020
2	10-01-2020
2	11-01-2020
2	12-01-2020
2	13-01-2020
3	20-01-2020
3	21-01-2020
3	22-01-2020
3	23-01-2020
3	24-01-2020
3	25-01-2020
3	26-01-2020
3	27-01-2020

In PQ, I have managed to to this by adding a custom column containing a list between the two dates for each project, and the expanded the lists of the column. However, due to performance, this needs to be computed in DAX.

Thanks in advance for your time and input! 

[dedelman_clng]

Hi @Anonymous -

You can use the GENERATE function

TimeSeries =
GENERATE (
    Project,
    FILTER (
        ALL ( DateTab[Date] ),
        DateTab[Date] <= Project[StatusReportDateMax]
            && DateTab[Date] >= Project[TimeRegistrationDateMin  ]
    )
)

Hope this helps

David

[jale_ahmad]

David, where should I paste the "GENERATE" code? Should it be pasted in New Query?

[dedelman_clng]

No, you will put it in a "New Table" DAX formula.