cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver IV
Resolver IV

Count # of consecutive times a column retains the same value

I need to track the # of times a column has a set value for by [Product Name] and [Date].

The column that needs to be tracked is the [BuyBoxStatus] column.

Once the value in [BuyBoxStatus] changes, the count would restart back to 1 again.

The intended result is the last column [Days in Current Status]

 

Example: 

-ProductName--BuyBoxStatus--Date--Days in Current Status-
A212/7/20201
 B112/7/20201
A212/8/20202
B112/8/20202
A212/9/20203
B112/9/20203
A512/10/20201
B312/10/20201
A512/11/20202

 

How can i achieve this either in a measure or Calculated Column?

 

 

1 ACCEPTED SOLUTION
Super User IV
Super User IV

I'm not surprised it is slow.  There is a lot of calculation going on, and I didn't know how many rows you have in your data.  Here is the measure version of it.  It works in a table visual with your product name column, and with or without the Date column from that table.  Of course, it will be much faster with just the product name (it will show the days it has been in the current status).

 

Days Current Status =
VAR vCurrentStatus =
    LASTNONBLANKVALUE (
        'Status'[-Date-],
        MAX ( 'Status'[-BuyBoxStatus-] )
    )
VAR vLatestDate =
    MAX ( 'Status'[-Date-] )
VAR vMinDateSameStatus =
    CALCULATE (
        MIN ( 'Status'[-Date-] ),
        FILTER (
            ALL ( 'Status'[-Date-] ),
            'Status'[-Date-] <= vLatestDate
        ),
        'Status'[-BuyBoxStatus-] = vCurrentStatus
    )
VAR vPrevDate =
    CALCULATE (
        MAX ( 'Status'[-Date-] ),
        FILTER (
            ALL ( 'Status'[-Date-] ),
            'Status'[-Date-] < vLatestDate
        ),
        'Status'[-BuyBoxStatus-] <> vCurrentStatus
    )
VAR vResult =
    IF (
        vPrevDate > vMinDateSameStatus,
        DATEDIFF (
            vPrevDate,
            vLatestDate,
            DAY
        ),
        DATEDIFF (
            vMinDateSameStatus,
            vLatestDate,
            DAY
        ) + 1
    )
RETURN
    vResult

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
Super User IV
Super User IV

I'm not surprised it is slow.  There is a lot of calculation going on, and I didn't know how many rows you have in your data.  Here is the measure version of it.  It works in a table visual with your product name column, and with or without the Date column from that table.  Of course, it will be much faster with just the product name (it will show the days it has been in the current status).

 

Days Current Status =
VAR vCurrentStatus =
    LASTNONBLANKVALUE (
        'Status'[-Date-],
        MAX ( 'Status'[-BuyBoxStatus-] )
    )
VAR vLatestDate =
    MAX ( 'Status'[-Date-] )
VAR vMinDateSameStatus =
    CALCULATE (
        MIN ( 'Status'[-Date-] ),
        FILTER (
            ALL ( 'Status'[-Date-] ),
            'Status'[-Date-] <= vLatestDate
        ),
        'Status'[-BuyBoxStatus-] = vCurrentStatus
    )
VAR vPrevDate =
    CALCULATE (
        MAX ( 'Status'[-Date-] ),
        FILTER (
            ALL ( 'Status'[-Date-] ),
            'Status'[-Date-] < vLatestDate
        ),
        'Status'[-BuyBoxStatus-] <> vCurrentStatus
    )
VAR vResult =
    IF (
        vPrevDate > vMinDateSameStatus,
        DATEDIFF (
            vPrevDate,
            vLatestDate,
            DAY
        ),
        DATEDIFF (
            vMinDateSameStatus,
            vLatestDate,
            DAY
        ) + 1
    )
RETURN
    vResult

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Super User IV
Super User IV

This column expression should work even if a product repeats a status.  Replace table/column names with your actual ones.

 

Days This Status =
VAR thisdate = 'Status'[-Date-]
VAR thisstatus = 'Status'[-BuyBoxStatus-]
VAR mindatesamestatus =
    CALCULATE (
        MIN ( 'Status'[-Date-] ),
        ALLEXCEPT (
            'Status',
            'Status'[-ProductName-]
        ),
        'Status'[-BuyBoxStatus-] = thisstatus,
        'Status'[-Date-] <= thisdate
    )
VAR maxdatediffstatus =
    CALCULATE (
        MAX ( 'Status'[-Date-] ),
        ALLEXCEPT (
            'Status',
            'Status'[-ProductName-]
        ),
        'Status'[-BuyBoxStatus-] <> thisstatus,
        'Status'[-Date-] < thisdate
    )
VAR finaldate =
    IF (
        maxdatediffstatus > mindatesamestatus,
        maxdatediffstatus,
        mindatesamestatus
    )
RETURN
    DATEDIFF (
        finaldate,
        thisdate,
        DAY
    ) + 1

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi @mahoneypat , thanks for the solution!!

Unfortunetly it is taking quite a long time to process.
You think if I tried, I could replicate this formula in a measure using virtual tables?
this measure would simply drop in a table with productId.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors