Count of last update per day

[BlackBird]

I am working on a covid-19 report of suspected and confirmed cases for my company contracts, and I need to count the amount of cases for each day, based on LastUpdate.

My dataset looks like this

LastUpdate	Contract	EmployeeName	Suspected	Confirmed
01/04/2020	A	EmployeeA	No	No
01/04/2020	A	EmployeeB	No	No
01/04/2020	A	EmployeeC	No	No
01/04/2020	B	EmployeeD	Yes	No
01/04/2020	B	EmployeeE	No	No
01/04/2020	C	EmployeeF	No	No
01/04/2020	C	EmployeeG	No	No
01/04/2020	C	EmployeeH	Yes	No
02/04/2020	A	EmployeeA	No	No
02/04/2020	A	EmployeeB	Yes	No
02/04/2020	A	EmployeeC	No	No
02/04/2020	B	EmployeeD	Yes	No
02/04/2020	B	EmployeeE	No	No
03/04/2020	A	EmployeeA	No	No
03/04/2020	A	EmployeeB	Yes	No
03/04/2020	A	EmployeeC	No	No
03/04/2020	B	EmployeeD	Yes	No
03/04/2020	B	EmployeeE	No	No
03/04/2020	C	EmployeeF	No	No
03/04/2020	C	EmployeeG	No	No
03/04/2020	C	EmployeeH	No	Yes
04/04/2020	B	EmployeeD	Yes	No
04/04/2020	B	EmployeeE	No	No
04/04/2020	C	EmployeeF	No	No
04/04/2020	C	EmployeeG	No	No
04/04/2020	C	EmployeeH	No	Yes

and I'm trying to output this

Date	Suspected	Confirmed
01/04/2020	2	0
02/04/2020	3	0
03/04/2020	2	1
04/04/2020	2	1

Not all contracts report everyday, in this case I need to count the last date it has updated.

Like above where on 02/04, contracts A and B reported 2 suspected cases + contract C update from 01/04 with 1 suspected case.

I don't now how to aproach this, can you help me?

[Greg_Deckler]

OK, this one was tricky, but I believe I have it, see attached PBIX, the basic pattern is:

Suspected Measure = 
    VAR __Date = MAX('Table'[LastUpdate])
    VAR __Table =
        ADDCOLUMNS(
            SUMMARIZE(
                FILTER(ALL('Table'),[LastUpdate]<=__Date),
                [Contract],
                "LastDate",MAX('Table'[LastUpdate])
            ),
            "Suspected",COUNTROWS(FILTER(ALL('Table'),'Table'[Contract]=EARLIER([Contract]) && [LastUpdate] = [LastDate] && [Suspected]="Yes"))
        )
RETURN
    SUMX(FILTER(__Table,[LastDate] <= __Date),[Suspected])

[BlackBird]

Thanks Greg it worked perfectly! 

But it didn't work with filters the way I thought... and I guess I should have mentioned it on the post, I'm sorry.

I have a separated table with all Contracts, with a Management column wich I use on a slicer.

Like this:

Contract	Management
A	M1
B	M1
C	M2

When I use the slicer, instead of calculating the number of suspected/confirmed cases of that management, it is showing the whole number for the days in wich the contracts of that management have updated.

How do I change it?

[Icey]

Hi @BlackBird ,

Replace ALL() with ALLSELECTED() in the measures.

Suspected Measure = 
VAR __Date =
    MAX ( 'Table'[LastUpdate] )
VAR __Table =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( ALLSELECTED ( 'Table' ), [LastUpdate] <= __Date ), ------changed
            [Contract],
            "LastDate", MAX ( 'Table'[LastUpdate] )
        ),
        "Suspected", COUNTROWS (
            FILTER (
                ALLSELECTED ( 'Table' ),                         --------------changed
                'Table'[Contract] = EARLIER ( [Contract] )
                    && [LastUpdate] = [LastDate]
                    && [Suspected] = "Yes"
            )
        )
    )
RETURN
    SUMX ( FILTER ( __Table, [LastDate] <= __Date ), [Suspected] ) + 0

Confirmed Measure =
VAR __Date =
    MAX ( 'Table'[LastUpdate] )
VAR __Table =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( ALLSELECTED ( 'Table' ), [LastUpdate] <= __Date ), --------changed
            [Contract],
            "LastDate", MAX ( 'Table'[LastUpdate] )
        ),
        "Confirmed", COUNTROWS (
            FILTER (
                ALLSELECTED ( 'Table' ),        --------------------------------changed
                'Table'[Contract] = EARLIER ( [Contract] )
                    && [LastUpdate] = [LastDate]
                    && [Confirmed] = "Yes"
            )
        )
    )
RETURN
    SUMX ( FILTER ( __Table, [LastDate] <= __Date ), [Confirmed] ) + 0

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

I'll have to take a look, it was complex enough that it took a good half hour to work through it so I'll have to add that to the model and see what needs to be done.