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 

I still don't understand, based on my understanding, the result should be

date range open(id) close(id) open count close count
<=2018/6/25 1   1 0
<=2018/12/12 1 1 1 1
<=2018/12/31   1 0 1
<=2019/3/30   1,2 0 2

 

Best Regards
Maggie

 

 

SethuPower's avatar
SethuPower
Frequent Visitor
6 years ago

Hi Maggie, 

Correction only on 12-12-2018 and others look correct

date rangeopen(id)close(id)open countclose count
<=2018/6/251 10
<=2018/12/12 1 1
<=2018/12/31 101
<=2019/3/30 1,202

 

I tried below code and it works for me only when i put this measure in a visual table and add ID column (both to values) it shows correct value. Now i want to show the total / count of what is displayed in table in a CARD. Not sure how to get the total / count of this measure 

RunningVersion = 

Var Temp = CALCULATE(
                    MAX( Table1[Version]) ,
                    FILTER( Table1,
                    MAXX( FILTER( Table1, EARLIER( Table1[ID] ) = Table1[ID] ),1)
                        )
                    )

Var X = CALCULATE( FIRSTNONBLANK(Table1[Status],1),
                        FILTER(Table1, Table1[ID] = Table1[ID]),
                        FILTER(Table1, Table1[Version] = Temp )
                        )
     
Return X