Calculate sustained values in the last x number of days

[AcsinteClaudiu]

Hello All,

I am stuck with a problem for days and any kind of help will be much appreciated!

I have some data about de COIVD-19 Community Spread for all USA Counties. What I need to do is to calculate for how many days a label was sustained from the last date available

For example: Let’s say that the last date (so the most recent one) is 11 – Jul – 2021. I need to have on the first column the “County Name”, on the second column the “Color label” for this date (Green, Yellow, Orange or Red), and on the third column, I need to have the number of days for how long that label was sustained. By sustained, I mean the same value without interruption. So, if today = Green and in the last 10 days the value was Green => 11. But if Today = Yellow and in the last 10 days was Green => 1.

Here you can find the pbix and the file source: https://we.tl/t-491CpVLqeS   . Covid-19 Community Spread data was downloaded from : https://globalepidemics.org/key-metrics-for-covid-suppression/ 

Any suggestions?

Thank you very much!

[Icey · Accepted solution]

Hi @AcsinteClaudiu ,

You can create a calculated column or measure like below. Please check if they meet your requirements.

Column:

Column = 
VAR CurrentDate = [Date]
VAR CurrentCountry = [County]
VAR CurrentColor = [Color Code]
VAR PreviousColor =
    LOOKUPVALUE (
        [Color Code],
        [Date], CurrentDate - 1,
        [County], CurrentCountry,
        [Color Code], CurrentColor
    )
VAR PreviousRow_DifferntColor =
    CALCULATE (
        MAX ( [Date] ),
        FILTER (
            'US County Risk Level',
            'US County Risk Level'[County] = CurrentCountry
                && 'US County Risk Level'[Date] < CurrentDate
                && 'US County Risk Level'[Color Code] <> CurrentColor
        )
    )
VAR Count_ =
    IF (
        ISBLANK ( PreviousColor ),
        1,
        CALCULATE (
            COUNTROWS ( 'US County Risk Level' ),
            FILTER (
                'US County Risk Level',
                [County] = CurrentCountry
                    && [Color Code] = CurrentColor
                    && [Color Code] = PreviousColor
                    && [Date] <= CurrentDate
                    && [Date] > PreviousRow_DifferntColor
            )
        )
    )
RETURN
    Count_

Measure:

Measure = 
VAR CurrentDate =
    MAX ( [Date] )
VAR CurrentCountry =
    MAX ( [County] )
VAR CurrentColor =
    MAX ( [Color Code] )
VAR PreviousColor =
    CALCULATE (
        MAX ( [Color Code] ),
        FILTER (
            ALLSELECTED ( 'US County Risk Level' ),
            'US County Risk Level'[Date] = CurrentDate - 1
                && [County] = CurrentCountry
                && [Color Code] = CurrentColor
        )
    )
VAR PreviousDate_DifferntColor =
    CALCULATE (
        MAX ( [Date] ),
        FILTER (
            ALLSELECTED ( 'US County Risk Level' ),
            'US County Risk Level'[County] = CurrentCountry
                && 'US County Risk Level'[Date] < CurrentDate
                && 'US County Risk Level'[Color Code] <> CurrentColor
        )
    )
VAR Count_ =
    IF (
        ISBLANK ( PreviousColor ),
        1,
        CALCULATE (
            COUNTROWS ( 'US County Risk Level' ),
            FILTER (
                ALLSELECTED ( 'US County Risk Level' ),
                [County] = CurrentCountry
                    && [Color Code] = CurrentColor
                    && [Color Code] = PreviousColor
                    && [Date] <= CurrentDate
                    && [Date] > PreviousDate_DifferntColor
            )
        )
    )
RETURN
    Count_

Result: 

Note: I only use part of data for test. For all data, it will take some time to run.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AcsinteClaudiu]

Hello @Icey ,

Many thanks for your solution. Until now everything seems to work like a charm!

Wish you all the best!