Forum Discussion
Additonal rows by ID with previous value for each day
ok try this version
Hi renlaforest ,
I suggest you to disconnect the relationship between two tables to use the continuous date.
Measure:
Count in continuous date =
VAR _GENERATE =
GENERATE ( 'Calendar', VALUES ( Query1[B005501HOME_NO] ) )
VAR _ADD1 =
ADDCOLUMNS (
_GENERATE,
"LASTDATE",
CALCULATE (
MAX ( Query1[CAST(TO_TIMESTAMP(B0055TIMESTAMP,'YYYY-MM-DDHH24:MI:SS.FF1')ASDATE)] ),
FILTER (
Query1,
Query1[B005501HOME_NO] = EARLIER ( [B005501HOME_NO] )
&& Query1[CAST(TO_TIMESTAMP(B0055TIMESTAMP,'YYYY-MM-DDHH24:MI:SS.FF1')ASDATE)]
<= EARLIER ( [Date] )
)
)
)
VAR _ADD2 =
ADDCOLUMNS (
_ADD1,
"STATUS",
CALCULATE (
MAX ( Query1[PPM_STATUS] ),
FILTER (
Query1,
Query1[B005501HOME_NO] = EARLIER ( [B005501HOME_NO] )
&& Query1[CAST(TO_TIMESTAMP(B0055TIMESTAMP,'YYYY-MM-DDHH24:MI:SS.FF1')ASDATE)]
= EARLIER ( [LASTDATE] )
)
)
)
RETURN
COUNTX (
FILTER (
_ADD2,
[Date] = MAX ( 'Calendar'[Date] )
&& [STATUS] = MAX ( Query1[PPM_STATUS] )
),
[B005501HOME_NO]
)Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the response. Maybe I should have been clearer with what I need. I basically need the count of current status (at the time). So, for these two homes in the example file, it would end up looking like this:
Your expected result doesn't match your sample data.
Thank you lbendlin - I'm not sure how the data doesn't match the expected result. Both start out as 0601 on September 13th, then are 0803 on September 16th, and one of them, 5750263, switches to 0805 on September 27th, while 5754142 remains 0803 until October 20th (when it switches to 0402 and stays there).
