Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
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)
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |