Forum Discussion

dazzerd's avatar
dazzerd
Frequent Visitor
5 years ago
Solved

Creating New Table from 2 Dates

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!

 

Need Help