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!
