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 ...
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.

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