Forum Discussion

renlaforest's avatar
renlaforest
Icon for Helper I rankHelper I
3 years ago
Solved

Carryover missing days results for all IDs in Power Query

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 stat...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    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's avatar
renlaforest
Icon for Helper I rankHelper I
3 years ago

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.