Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum value from one column checking if previous month the value was identical

Dear all,

 

In my table (see below) I have two cases, I would like to sum the cumulative days per Key over the different months, but only when the row is flagged as 1, if it reaches 0 in a certain point of time the sum starts over and stays zero until there is another flag with 1.  In the table below i also added the requested column:

 

DateYMKeyFlagDays in monthCalccalculation:
1-1-20191901A1316231+31
1-1-20191901B0310 
1-12-20181812A13131 
1-12-20181812B1316130+31
1-11-20181811A0300 
1-11-20181811B13030 
1-10-20181810A13131 
1-10-20181810B0310 

 

Can someone help me get the requested column calculation. Thanks.

 

 

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi Anonymous 

    Try this for a calculated column in your table (Table1). See it at work in the attached file.

    NewColumn =
VAR _PreviousZeroDate =
    LASTNONBLANK (
        CALCULATETABLE (
            DISTINCT ( Table1[Date] );
            ALLEXCEPT ( Table1; Table1[Key] );
            Table1[Flag] = 0;
            Table1[Date] < EARLIER ( Table1[Date] )
        );
        1
    )
RETURN
    IF (
        Table1[Flag] = 0;
        0;
        CALCULATE (
            SUM ( Table1[Days in month] );
            ALLEXCEPT ( Table1; Table1[Key] );
            Table1[Date] <= EARLIER ( Table1[Date] );
            Table1[Date] > _PreviousZeroDate
        )
    )

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous  - 

    Please try the following:

    Running Total With Reset = 
var currentmonth = max(Resetting[YM])
var lastzero = CALCULATE(MAX(Resetting[YM]), ALLEXCEPT(Resetting, Resetting[Key]), Resetting[YM] <= currentmonth, Resetting[Flag] = 0)
var calc = CALCULATE(SUM(Resetting[Days in month]), filter(ALLEXCEPT(Resetting, Resetting[Key]),Resetting[YM] > lastzero && Resetting[YM] <= currentmonth))
return IF(ISBLANK(calc),0,calc)

    Hope this helps,

    Nathan

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Hi Anonymous 

      Try this for a calculated column in your table (Table1). See it at work in the attached file.

      NewColumn =
VAR _PreviousZeroDate =
    LASTNONBLANK (
        CALCULATETABLE (
            DISTINCT ( Table1[Date] );
            ALLEXCEPT ( Table1; Table1[Key] );
            Table1[Flag] = 0;
            Table1[Date] < EARLIER ( Table1[Date] )
        );
        1
    )
RETURN
    IF (
        Table1[Flag] = 0;
        0;
        CALCULATE (
            SUM ( Table1[Days in month] );
            ALLEXCEPT ( Table1; Table1[Key] );
            Table1[Date] <= EARLIER ( Table1[Date] );
            Table1[Date] > _PreviousZeroDate
        )
    )
      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Anonymous 

        I believe you need a small change in your currentmonth variable. You need the YM value for the current row rather than for the whole table, so either:

        var currentmonth = CALCULATE(max(Resetting[YM]))

        based on what you already have or, more straightforwardly:

        var currentmonth = Resetting[YM]

        With that minor modification it should work and in fact your way of getting the last zero is actually more elegant and efficient than mine.

        Cheers