Add Missing Date Rows to Table based on ID, Date, and Status

[david_young]

Hello,

I am trying to generate a row for records between 2 non-incremental dates in DAX.

This is the dataset that I am currently working with:

       ID           dayMonthYear      status

100	13-5-2019	In Queue
100	14-5-2019	Closed
101	13-5-2019	In Queue
101	15-5-2019	Closed
102	13-5-2019	In Queue
102	16-5-2019	Escalate
102	18-5-2019	Closed

These are the results that I am trying to achieve:

    ID       dayMonthYear        status

100	13-5-2019	In Queue
100	14-5-2019	Closed
101	13-5-2019	In Queue
101	14-5-2019	In Queue
101	15-5-2019	Closed
102	13-5-2019	In Queue
102	14-5-2020	In Queue
102	15-5-2021	In Queue
102	16-5-2019	Escalate
102	17-5-2019	Escalate
102	18-5-2019	Closed

ID 102 is in status "In Queue" for 3 days, then moved to status "Escalate", then 2 days later moved to "Closed". Right now I am only tracking the dates when a a status changes. I want to be able to insert rows for all dates when a status is not "updated". Is this something I can do in DAX?

[david_young · Accepted solution]

Hi Everyone,

I was able to solve this using a table and a dynamic column.

Initial 'Daily Burndown' generates a table that assigns every date to an ID.

Daily Burndown = 

VAR myCalendar =
    CALENDAR (
        MIN ( Table[dayMonthYear] ),
        MAX ( Table[dayMonthYear] )
    )
VAR CJ =
    CROSSJOIN ( myCalendar, Table )
VAR WR =
    ADDCOLUMNS (
        SUMMARIZE ( CJ, [Date], [ID] ),
        "Update Date", LOOKUPVALUE ( Table[dayMonthYear],
            [dayMonthYear], [Date],
            [ID], [ID]
        )       
    )

RETURN
WR

Next I need to add a column to the new 'Daily Burndown' table to determine what the status is on the days that are not populated in the original table.

This code determines the last time a date changed and populates the blank rows with the earliest date before the next date. Once we have that date, we can use a LOOKUP from the original table the exact status and populate that in the row.

Report Status = 

VAR previousrow =
    TOPN (
        1,
        FILTER (
           'Daily Burndown',
            [ID] = EARLIER ( [ID] )
                && [Date] < EARLIER ( [Date] )
                && 'Daily Burndown'[Update Date] <> BLANK ()
        ),
        [Date], DESC
    )

VAR row_2 =
    IF (
        'Daily Burndown'[Update Date] = BLANK (),
        MINX ( previousrow, [Date] ),
        [Date]
    )

VAR look_up =
    LOOKUPVALUE (
        Table[Report Status],
        Table[ID], [ID],
        Table[dayMonthYear], row_2
    )
    
RETURN
    look_up

[nhoward]

another option to try:

Fill dates between dates with Power BI / Power Query — The Power User

[Anonymous]

@david_young 

hi David,

i have very similar problem to yours and making use of your code.

However the last "look_up" VAR give me an error:

"A table of multiple values was supplied where a single a value was expected."

I think it is because the filter [UPDATE DATE] <> BLANK () in "previousrow" VAR didnt work properly. I'm not sure how to fix it.

Can you please help?

This is the Return from the "row_2" VAR.

Thanks in advance.

ChimDen

[AlejandroVazque]

Hi All,

I have the same need and the osluion here works perfect for me when I have one year period, but if I exten this to my full data range (10 years) the memory blows up very quickly. Is there another solution that requires less memory ?

Thanks for your help

Alejandro

[brickhouse3539]

@david_young 

Hello David, 

This is exactly what I need but I'm not sure about how to use this portion of your daily burndown code.  Do all of these fields refer to your table you provided?  You have a daymonthyear column so I assume the first two in the code refer to the same column?  What about "[Date]", what does that mean?  

LOOKUPVALUE ( Table[dayMonthYear],
            [dayMonthYear], [Date],
            [ID], [ID]

[brickhouse3539]

@david_young 

I figured it out, this is awesome, thanks!!

[RahulPradhan25]

Hi,

I don't get what date fields are you using. Can you please describe this is a bit detail, it would help me very much.

Thanks,

Rahul.