topic Re: Date list with active projects based on start and enddate in DAX Commands and Tips

Date list with active projects based on start and enddate

Lucht — Wed, 22 Mar 2023 09:56:46 GMT

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

Re: Date list with active projects based on start and enddate

tt_and — Wed, 22 Mar 2023 10:30:50 GMT

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"

Re: Date list with active projects based on start and enddate

Lucht — Wed, 22 Mar 2023 10:41:40 GMT

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.

Re: Date list with active projects based on start and enddate

tt_and — Wed, 22 Mar 2023 11:04:29 GMT

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]) ) )