Carryover missing days results for all IDs in Power Query

[renlaforest]

Hi - I have a table that shows each time a home changes status:

As you can see, 4520778 became status 0601 on October 18, 2017. It then changed to 0803 on October 20th.

ID 4531969 became status 0803 on November 1st, and changed to 0808 on November 12th.

I'd like to have the status for all days listed for each of the IDs based on what it was the day before (until it changes). An example of what that would look like is below. Is this possible? Sample file included.

https://drive.google.com/file/d/1l2Ku45-ziW1j6pYRuGkYfPsl7OSwm0qZ/view?usp=drive_link

Thank you in advance!

[renlaforest]

Hi @Ashish_Mathur thank you for the reply!

The dataset only shows the changes in status. The status for 4520778 changed to 1002 on October 29th and was still that status until the status changed days later.

Basically, I have that dataset that shows the status changes, then there's another dataset that shows the changes in status for the same IDs, but for a different product. The home may have been 1002 (production) in this dataset on October 29th, 2017, but may have only been 1002 (production) in the second dataset days later. I would need that HOME_NO to count as being in production once the status is 1002 in both datasets, and need to be able to have a daily count of production (or other status) by day.

I could do that, if I was able to get both datasets to show in the Query Editor as I did in the example, since I'd be able to merge both datasets based on B0055STAT_CHNG_DAT B005501HOME_NO. 

I could then see that on October 31st, the home had a status of 1002 in the first dataset, and whatever status it was on that same day in the second dataset to figure out how to count the home that day.

Hope that makes sense.

[Ashish_Mathur]

Hi,

If you wish to merge the dataset, then you should do this in the Query Editor.  Merging cannot happen in the Data model.  Also, for ID 4520778, why does the date not stop at 10/29/2007?  Why does it go till 10/31/2007?

[lbendlin]

Did you want tis as a calculated column or a measure?

see attached. 

[renlaforest]

Hi @lbendlin, thank you for the reply! It would have to be a column, since I actually have to merge this dataset with a second similar dataset to come up with a combined status per day.

[lbendlin · Accepted solution]

Happy Cartesian Product!

Cross = ADDCOLUMNS(CROSSJOIN(CALENDAR(DATE(2022,9,1),DATE(2025,12,1)),VALUES(Historic[B005501HOME_NO])),"Status",
var d = [Date]
var h = [B005501HOME_NO]
var md = CALCULATE(max(Historic[B0055STAT_CHNG_DAT]),Historic[B0055STAT_CHNG_DAT]<=d,Historic[B005501HOME_NO]=h)
return CALCULATE(max(Historic[PPM_STATUS]),Historic[B0055STAT_CHNG_DAT]=md,Historic[B005501HOME_NO]=h))

You are looking at 1.7 MILLION rows with that approach.

[renlaforest]

Thank you soooooooo much!!!!! I really appreciate it @lbendlin