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 

Is my understanding correct?

It is to say, for date 2018/1/26, "Open" count  as 2, and "Closed" count as 0.

 

 
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

Hi Maggie,

 

Thanks for your reply. On 26-Jan-2018, Open and Close count both should be 0 as the first starting date (or minimum date) is 20-Jun-2018. I tried below to get the max version no for that date range and pull the status for that.. however it is picking the maximum of version no irrespective of slicer date and pulling the last status. (2 measures - one for Open Count and one for Closed Count)

 

 
LastOpenCount = 

 VAR Temp =

    ADDCOLUMNS(

            SUMMARIZE(Table1,

            Table1[ID],

            "LastVersion",MAX(Table1[Version])

            ), "CloseOpenC",

                Calculate(FIRSTNONBLANK(Table1[Status],1),

                        FILTER(Table1, Table1[ID]= Table1[ID]),

                        FILTER(Table1, VALUE(Table1[Version])=VALUE([LastVersion]))

                        )

      )

Return

Calculate(

DISTINCTCOUNT( [ID] ),

Filter(Temp, [CloseOpenC]="Open")

)