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 should get "Open" count as 1 and "Closed" count as Zero.

If date range is less than or equal to 31-Dec-2018, Then i should get "Open" count as 0 and "Closed" count as 1 (final status as per the date range should be considered)

If date range is less than or equal to 30-Mar-2019, Then i should get "Open" count as 1 and "Closed" count as 1

If date range is less than or equal to 15-Dec-2018, Then i should get "Open" count as 2 and "Closed" count as 0

 

Could you please help how my measure should be Zubair_Muhammad Ashish_Mathur 

(its getting double counted for me which i want to avoid)

 

IDVersionStatusDateStatus
1120-Jun-18Open
1220-Jun-18Open
1325-Jun-18Open
1427-Jul-18Closed
1531-Dec-18Closed
2115-Dec-18Open
2222-Feb-19Closed
2329-Mar-19Closed

 

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.

7 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity 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")

)

       

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity 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

       

       

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity 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
        Icon for Community Support rankCommunity 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.