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 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

  • 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
      New 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)