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

[Anonymous]

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:

Date	YM	Key	Flag	Days in month	Calc	calculation:
1-1-2019	1901	A	1	31	62	31+31
1-1-2019	1901	B	0	31	0
1-12-2018	1812	A	1	31	31
1-12-2018	1812	B	1	31	61	30+31
1-11-2018	1811	A	0	30	0
1-11-2018	1811	B	1	30	30
1-10-2018	1810	A	1	31	31
1-10-2018	1810	B	0	31	0

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

[Anonymous]

@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 · Accepted solution]

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]

@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

[Anonymous]

@AlB -

Thanks for the feedback. I should have clarified that I was creating a Measure. Most likely, @Anonymous  would prefer the Calculated Column route, unless it needs to be dynamic for some reason. 

@Anonymous  - The code I wrote was for a Measure. You could use the same code in a Calculated Column if you make the change that @AlB suggested.

Cheers!