Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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
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
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
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
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]
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
19 | |
18 | |
18 | |
17 |
User | Count |
---|---|
37 | |
25 | |
18 | |
17 | |
13 |