Forum Discussion

renlaforest's avatar
renlaforest
Helper I
3 years ago
Solved

Additonal rows by ID with previous value for each day

Hi - I have a dataset that provides historic values for each ID. So, the status for ID 574142 went from 0601 on September 13th to 0803 to September 16th.

My goal is to have the count of each status by day in a matrix. Using the same ID as an example, the matrix would count that ID as 0601 on the 13th, 14th and 15, and then would start counting under the 0803 status as of the 16th, until the ID shows up with a different status again.

Is there either a measure that I can write in DAX, or something that I can do in Power Query that would help with this? I appreciate the help!

I created an example file here: https://drive.google.com/file/d/1IxatzxiXvVAx3WNWcS0LRb4L3sTwLEXo/view?usp=drive_link

Need Help
Tips and Tricks

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

    • renlaforest's avatar
      renlaforest
      Helper I
      3 years ago

      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: