Cumulated until today

[DavidB023]

Hi everybody !

I want to cumulate value by year in order to comparate at today, which variation I have on an electric consumption for example.

In fact I have my consumption month by month for 2017 and 2018, and on my dashboard I want the +/- consumption between this year and last year.

Thanks

Best regards
David

[Soulus101 · Accepted solution]

This worked for me (assumes you have a [date] column and a [date (Year)] column):

SumtoMonth:=
VAR lastdt = CALCULATE(LASTDATE(Table[Date],all(Table[Year])) RETURN

VAR lastm = MONTH(lastdt) RETURN

VAR DateSet = DATESBETWEEN(Table[Date],
                           DATE(MAX(Table[Year]),1,1),
                           DATE(MAX(Table[Year]),lastm,1))
RETURN

CALCULATE(Sum([Consumption]),DateSet)

[Soulus101 · Accepted solution]

This worked for me (assumes you have a [date] column and a [date (Year)] column):

SumtoMonth:=
VAR lastdt = CALCULATE(LASTDATE(Table[Date],all(Table[Year])) RETURN

VAR lastm = MONTH(lastdt) RETURN

VAR DateSet = DATESBETWEEN(Table[Date],
                           DATE(MAX(Table[Year]),1,1),
                           DATE(MAX(Table[Year]),lastm,1))
RETURN

CALCULATE(Sum([Consumption]),DateSet)

[Soulus101 · Accepted solution]

Just to take it a step further, the below will create the difference measure you were after; essentially it calculates the value for last year against this year, and then subtracts it from this year (returns a blank in the first year)...

SumtoMonth:=
VAR lastdt = CALCULATE(LASTDATE(Table[Date],all(Table[Year])) RETURN

VAR lastm = MONTH(lastdt) RETURN

VAR DateSet = DATESBETWEEN(Table[Date],
                           DATE(MAX(Table[Year]),1,1),
                           DATE(MAX(Table[Year]),lastm,1))

VAR DateSetLast = DATESBETWEEN(Table[Date],
                           DATE(MAX(Table[Year])-1,1,1),
                           DATE(MAX(Table[Year])-1,lastm,1))

RETURN

VAR LastConsumption = CALCULATE(SUM([Consumption]),
                                DateSetLast,ALL(Table[Year]))
RETURN

IF(ISBLANK(LastConsumption),
   BLANK(),
   CALCULATE(Sum([Consumption]),DateSet) - LastConsumption)