Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
david_young
Regular Visitor

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

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

10013-5-2019In Queue
10014-5-2019Closed
10113-5-2019In Queue
10115-5-2019Closed
10213-5-2019In Queue
10216-5-2019Escalate
10218-5-2019Closed

 

 

These are the results that I am trying to achieve:

 

    ID       dayMonthYear        status

10013-5-2019In Queue
10014-5-2019Closed
10113-5-2019In Queue
10114-5-2019In Queue
10115-5-2019Closed
10213-5-2019In Queue
10214-5-2020In Queue
10215-5-2021In Queue
10216-5-2019Escalate
10217-5-2019Escalate
10218-5-2019Closed

 

 

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?


1 ACCEPTED SOLUTION
david_young
Regular Visitor

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

 

 

View solution in original post

7 REPLIES 7
david_young
Regular Visitor

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

 

 

Anonymous
Not applicable

@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.

 

ChimDen_0-1622080243200.png

 

Thanks in advance.

ChimDen

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

 

@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]

 

@david_young 

 

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

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.