Count Status as per the Date Filter

[SethuPower]

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)

ID	Version	StatusDate	Status
1	1	20-Jun-18	Open
1	2	20-Jun-18	Open
1	3	25-Jun-18	Open
1	4	27-Jul-18	Closed
1	5	31-Dec-18	Closed
2	1	15-Dec-18	Open
2	2	22-Feb-19	Closed
2	3	29-Mar-19	Closed

[v-juanli-msft]

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]

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 · Accepted solution]

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]

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]

Hi Maggie, 

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

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

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]

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]

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")

)