Counting days between a change

[greenskmachine2]

Hi there,

I have measures for Percent, and Percent previous day. I also have a measure to flag if the daily change went up or down. 

What I am wanting to do is write a measure that counts the number of days between the start of each change, as shown in the image. 

Can you please assist me in helping to write this measure? Thanks. 

[Anonymous · Accepted solution]

Hi @greenskmachine2 ,

Check the following measure:

Consecutive Days Measure = 
VAR CurrentDate = MAX('Table'[Date])
VAR CurrentFlag = MAX('Table'[Percent Change Flag])

VAR PreviousDates =
    FILTER(
        ALL('Table'),
        'Table'[Date] < CurrentDate
    )

VAR LastChangeDate =
    MAXX(
        FILTER(
            PreviousDates,
            'Table'[Percent Change Flag] <> CurrentFlag
        ),
        'Table'[Date]
    )

VAR StartDate =
    IF(ISBLANK(LastChangeDate), MIN('Table'[Date]), LastChangeDate)

VAR ConsecutiveDays =
    DATEDIFF(StartDate, CurrentDate, DAY)

RETURN
IF(NOT ISBLANK(CurrentFlag), ConsecutiveDays, BLANK())

Result:

Best regards,

Joyce

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