Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need help reset count

Hi All, I've tried to calculate the total infected and recovered users every month, the total recovered doesn't matter but the total infected when the same users are infected again after recovery th...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    I'd recommend looking at whatever the last status was.

     

    Try something like this:

    CountInfected =
VAR LastStatusDates =
    ADDCOLUMNS (
        VALUES ( Summary[User] ),
        "@LastStatusDate", CALCULATE ( MAX ( Summary[Date] ) )
    )
RETURN
    SUMX (
        LastStatusDates,
        LOOKUPVALUE (
            Summary[Infected],
            Summary[User], Summary[User],
            Summary[Date], [@LastStatusDate]
        )
    )

     

parry2k's avatar
parry2k
Super User
4 years ago

AlexisOlson got it. I missed that. Thanks for pointing that out.

 

Anonymous Here is a measure.

 

Total Infected = 
VAR __table = TOPN ( 1, ALLSELECTED ( 'Table' ), CALCULATE ( MAX ( 'Table'[Date] ), VALUES ( 'Table'[User] ) ), DESC )
VAR __totalInfected = 
CALCULATE ( 
    SUM ( 'Table'[Infected] ), 
    KEEPFILTERS ( __table )
) -
CALCULATE (
    SUM ( 'Table'[Recovered] ),
    KEEPFILTERS ( __table )
)
RETURN IF ( __totalInfected  > 0, __totalInfected )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

It works as required. Great Support. Thanks, parry2k