Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
renlaforest
Helper I
Helper I

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

Screenshot 2023-06-01 141812.png

1 ACCEPTED SOLUTION

ok try this version

View solution in original post

6 REPLIES 6
v-rzhou-msft
Community Support
Community Support

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.

vrzhoumsft_0-1685952487975.png

 

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:

Screenshot 2023-06-05 085430.png

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).

ok try this version

lbendlin
Super User
Super User

To report on things that are not there you need to use disconnected tables and/or crossjoins

lbendlin_0-1685907895841.png

see attached.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.