Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need cumulative sum with condition

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
 

 

Need Help
  • Icey's avatar
    Icey
    5 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

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

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    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.