Dax Measure to calculate number of days status is red

[Anonymous]

I need help with writing a dax measure to calculate the number of days the status is red.

Data

For my data, the status that is red = 1

I have a data that is coming in day/night. I want to calculate from today how many days it have been red (both day and night must be red). If the lastest data is not red then it will be 0. 

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ，

According to your description, I create a sample.

I add more rows to test. If your date and Day/Night are in the same column, firstly split the column in Power Query, otherwise the column can't be in the date type.

Here's my solution, create a measure.

COUNT =
VAR _T =
    ADDCOLUMNS (
        'Table',
        "Red",
            IF (
                COUNTROWS (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Date] = EARLIER ( 'Table'[Date] )
                            && 'Table'[B status] <> 1
                    )
                ) = 0,
                1
            )
    )
VAR _Max =
    MAXX ( FILTER ( _T, [B status] <> 1 ), [Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Date] ),
        FILTER ( _T, [Red] = 1 && [Date] > _Max && [Date] <= TODAY () )
    )

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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