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.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago

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?