Forum Discussion

SethuPower's avatar
SethuPower
Frequent Visitor
6 years ago
Solved

Count Status as per the Date Filter

Hello All,   My input data looks like below. I have a slicer with Date Range. Depending on the Date filter, my output to change    Eg. If the date range is less than or Equal to 25-Jun-18, Then i...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi SethuPower 

    In addition to my previous reply,

    Create measures

    distincount status per day =
CALCULATE (
    DISTINCTCOUNT ( Table1[Status] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        Table1[ID] = MAX ( Table1[ID] )
            && Table1[StatusDate] = MAX ( Table1[StatusDate] )
    )
)

    Modify measure

    open =
VAR countopen =
    COUNTX (
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[ID] <> MAX ( Table1[ID] )
                && [open/closed] = "Open"
        ),
        [open/closed]
    )
RETURN
    IF ( [distincount status per day] > 1, countopen - 1, countopen )

    Best Regards
    Maggie
     
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi SethuPower 

Create measures below

Max date each id = CALCULATE(MAX(Table1[StatusDate]),FILTER(ALLSELECTED(Table1),Table1[ID]=MAX(Table1[ID])))

open/closed = IF(MAX(Table1[StatusDate])=[Max date each id],MAX(Table1[Status]))

open = COUNTX(FILTER(ALLSELECTED(Table1),[open/closed]="Open"),[open/closed])

closed = COUNTX(FILTER(ALLSELECTED(Table1),[open/closed]="Closed"),[open/closed])
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SethuPower's avatar
SethuPower
Frequent Visitor
6 years ago

Thanks Maggie for your efforts. Appreciate a lot

All works fine with your code except one part which is where i am also stuck.

there are cases where on a single date we have two status i.e eg 20-June-18, Version 3 was "Open" and 20-Jun-18, Version 4 has "Closed". Here we need to count only "closed" as 1 and exclude "open" as the latest version has "Closed". however above code includes total of both versions as it fall on the same date.  (it shows Open also as 1 and Closed also as 1"

 

Any idea where we are missing in that situation

 

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi SethuPower 

    In addition to my previous reply,

    Create measures

    distincount status per day =
CALCULATE (
    DISTINCTCOUNT ( Table1[Status] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        Table1[ID] = MAX ( Table1[ID] )
            && Table1[StatusDate] = MAX ( Table1[StatusDate] )
    )
)

    Modify measure

    open =
VAR countopen =
    COUNTX (
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[ID] <> MAX ( Table1[ID] )
                && [open/closed] = "Open"
        ),
        [open/closed]
    )
RETURN
    IF ( [distincount status per day] > 1, countopen - 1, countopen )

    Best Regards
    Maggie
     
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.