Date list with active projects based on start and enddate

[Lucht]

Hello PBI community,

I have this table:

Project dStart          dEnd
001      24-01-2023 10-02-2023
002      25-01-2023 31-01-2023
003      05-02-2022 11-02-2023

I would like to create a long date-list of it. With all the dates that have active projects. This would mean that dates should be added multiple time if there are multiple active projects on that date. It should look something like this:

Date             Project
24-01-2023  001
25-01-2023  001
25-01-2023  002
26-01-2023  001
26-01-2023  002

[tt_and]

Have you considered doing this with Power Query?

let
    Source = #"I have this data",
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Project"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "dName"}})
in
    #"Renamed Columns"

[Lucht]

I've tried this. But it will only provide me a list with all the start and end dates. I'm trying to find a solution that also adds all the in between dates.

[tt_and]

Oh, I see. You would need to generate a list of dates and merge to the table somehow to fill the gaps. Depending on what you want to do, there might be a better way to get the dates without having to materialize them. E.g. use a detatched calendar and dax if you want to count or sumarize within periods. This way you can drag the Date column from the Calendar table and the measure from the project table into a visual:

Count projects = 
CALCULATE (
    sumx(
        DISTINCT('I have this data'[Project]),1
    ),
        FILTER (
            'I have this data',
            'I have this data'[dEnd] > MAX ( Calendar[Date] )&&
            'I have this data'[dStart] <= MAX ( Calendar[Date])
        )
)