Creating New Table from 2 Dates

[dazzerd]

Hello I'm new to Power BI but have extensive experience in Qlik. My job at the moment is to replicate some qlik apps into power bi.

We have a data set which can be simplified as such, clearly there will be 10000s of rows rather than 1

Start Date, End Date, ID, Value

01/01/2021, 31/01/2021, 1, 999

01/02/2021, 05/02/2021, 2, 500

What I want to end up with is a table such as this:

Date, ID, Value

01/01/2021, 1, 999

02/01/2021, 1, 999

03/01/2021, 1 ,999

.........

31/01/2021, 1, 999

01/02/2021, 2, 500

02/02/2021, 2, 500

03/02/2021, 2, 500

04/02/2021, 2, 500

05/02/2021, 2, 500

In other words a row for each date between the start and end dates for every identifier

In qlik this is simple:

WAR_FACTS:
LOAD
%_TICKETID,
DATE([WAR Start Date] + ITERNO() - 1) AS [Fact Date],
'Predicted Exposure' AS [Fact Type],
[WAR Value Of Risk] AS #_EXPOSURE_VALUE,
1 AS #_FACTCOUNT
RESIDENT WORKING_AT_RISK WHILE DATE([WAR Start Date] + ITERNO()-1) <= [WAR End Date];

I have thought about using the calendar functionality in dax such as:

WARFACTS = 
VAR MinDate = min(Start Date)
VAR MaxDate = max(End Date)
RETURN
 ADDCOLUMNS (
 CALENDAR (MinDate,MaxDate),
 "Prdeicted Exposure", "Predicted Exposure"
 "%_TICKETID", tbl.TicketID
 )

But that fails as I can't pull the correct ticket id from the source table. I dont actually think it would work anyway as we to create a row for each ID between its start and end date.

Any ideas as I'm stumped!

[amitchandak · Accepted solution]

@dazzerd , refer to the file attached after signature if that can help

[dazzerd]

Excellent had to modify it a bit for my needs but it works/ Not quite as elgant as qlik script I don;t think but thats fine 🙂