Forum Discussion
Cumulated until today
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
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)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)
2 Replies
- Soulus101New Member
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)- Soulus101New Member
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)