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 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's avatar
    lbendlin
    Icon for Super User rankSuper User
    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

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