Need cumulative sum with condition

[Anonymous]

Hi All, 

Hope everyone is well and safe in this pandemic. Iam new to Power BI and working on few calculations that need your help.

As shown above i have week and weekly confirmed cases as columns, "Up/down Trend Test" is a measure that returns if the cases from current and previous week are decreased or increased. logic is as below.

Up/Down Trend Test =

var a=sum(districts[Weekly Confirmed Cases])

var d=MAXX(districts,districts[Week])

var b=CALCULATE(sum(districts[Weekly Confirmed Cases]),districts[Week]=d-1)

var c=IF(a<b,"Down","Up")

return c

Now my requirement is i want to return the count of consecutive downs . if cases are decreasing constantly for few weeks it should cumulatively count the weeks , if there is any up in middle, it should stop and again when the next down comes it should start again from 1.

Please help me with above requirement.

Expected output

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

StartWeek =
VAR ThisWeek =
    MAX ( districts[Week] )
VAR LastWeek = ThisWeek - 1
VAR ThisTrend = [Up/Down Trend Test]
VAR LastTrend =
    IF (
        ThisWeek > 1,
        CALCULATE ( [Up/Down Trend Test], districts[Week] = LastWeek )
    )
RETURN
    IF ( ThisTrend <> LastTrend, ThisWeek )

consecutive count =
VAR CalStartWeek =
    MAXX (
        FILTER (
            ALLSELECTED ( districts[Week] ),
            districts[Week] <= MAX ( districts[Week] )
        ),
        [StartWeek]
    )
VAR ThisWeek =
    MAX ( districts[Week] )
RETURN
    CALCULATE (
        COUNTROWS ( districts ),
        districts[Week] >= CalStartWeek
            && districts[Week] <= ThisWeek
    )

Best Regards,

Icey

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

[amitchandak]

@Anonymous ,

a new colum=

var _last = maxx(filter(Table, [Week] =earlier([Week]) -1) ,[Weekly confirmed cases])

return

if([Weekly confirmed cases] >_last , "up", "down")

[Anonymous]

@amitchandak 

I cant use this for calculated column because i want these values to be changed based on selection in slicers. moreover the formula you suggested seems to return down/up , but i need to print number which gives consecutive downs

[amitchandak]

@Anonymous , Create week Rank if you have year week, or use week. But prefer to move week to a separate table

new column in date/week table

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures  - use week in place week rank if needed

This Week = CALCULATE(sum('Table'[Weekly confirmed cases]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Weekly confirmed cases]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Status measure =

if([ThisWeek] >[Last Week], "up", "down")