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

     

AlexisOlson's avatar
AlexisOlson
Super User
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]
        )
    )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Perfect, It works as required too in different ways. Great Support. Thanks AlexisOlson