Forum Discussion
DavidB023
8 years agoHelper III
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 ...
- 8 years ago
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) - 8 years ago
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)
Soulus101
8 years agoNew 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)
- Soulus1018 years agoNew 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)