Power Query: how to create missing date

[PBI_newuser]

Hi,

I want to fill in the missing [Assigned Date].

How to create the [Assigned Date] with the below criteria:

If there is no [Assigned Date], then use [Created Date],

but if the [Created Date] is after [Onsite Date],

then use [Onsite Date] as [Assigned Date].

Assigned Date	Created Date	Onsite Date
2/27/2020	2/26/2020	10/13/2020
-	9/2/2020	9/8/2020
-	9/12/2020	9/5/2020

[Anonymous]

You can do this in DAX by creating a new table with all of the dates in your range as follows: FullTable = ADDCOLUMNS( CALENDAR(MIN(Table1[Date]), MAX(Table1[Date])), "Quantity", LOOKUPVALUE( Table1[Quantity], Table1[Date], MAXX( FILTER(Table1, Table1[Date] <= EARLIER([Date])), [Date] ) ) )

The CALENDAR function gives you a range of dates from you minimum to maximum dates in your original table. From there, we add a new column, Quantity, and define it as the value we get when looking up the Quantity in the original table for the date that was the maximum date occurring on or before the date in the current row.

[Pragati11]

Hi @PBI_newuser ,

You can create a calculated column using the DAX below:

NewCol = IF([Assigned Date] = BLANK(), [Created Date], IF([Created Date] > [Onsite Date], [Onsite Date],  [Assigned Date]))

Just make sure all the columns are in DATE format.

Thanks,

Pragati

[PBI_newuser]

Hi @Pragati11 ,

It doesn't work. 

[Pragati11 · Accepted solution]

Hi @PBI_newuser ,

May be modify the condition as follows and the try:

NewCol = IF([Assigned Date] = BLANK() && [Created Date] < [Onsite Date], [Created Date],

IF([Assigned Date] = BLANK() && [Created Date] > [Onsite Date], [Onsite Date],  [Assigned Date]))

Thanks,

Pragati