Forum Discussion

DavidB023's avatar
DavidB023
Helper III
8 years ago
Solved

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 ...
  • Soulus101's avatar
    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)
  • Soulus101's avatar
    Soulus101
    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)