Forum Discussion

greenskmachine2's avatar
greenskmachine2
Frequent Visitor
1 year ago
Solved

Counting days between a change

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's avatar
    Anonymous
    1 year ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.