Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Adding too measures together

Hello

 

I'm creating a chart showing actual expenditure to date and future forecast.

 

I've created two separate measures for:

 

Cumulative payments to date 

 

Cumulative payments = var contextDate = MAX('Calendar'[Date]) var output = CALCULATE( SUM('Money Transfers 201819'[MT amount for charts]), FILTER(ALL('Calendar'[Date]), 'Calendar'[Date] <= contextDate) ) RETURN IF( contextDate <= Today(), output )
 
Cumulative future forecasts
 
Cumulative forecasts in future = CALCULATE(SUM('Forecast Report Oct 2016'[Forecast amount]),FILTER(ALL('Calendar'[Date]),AND('Calendar'[Date]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1),'Calendar'[Date]<=MAX('Calendar'[Date]))))
 
 
When i add both measures to a chart there are two separate lines. 
 
What i would like to do is create a third measure that basically sums these two together. Eg, at the moment, if there are no December forecasts, the cumulative forecast line starts at 0. I would like the latest month's cumulative payments to be added to the future forecasts so the line basically 'keeps going up' rather than dropping down for zero forecasts.
 
I hope this makes sense, i'm only a beginner user. If anyone could help me with a measure to 'add' these separate measures together than would be amazing!
 
Thanks!
Need Help

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    I believe that should just be:

     

    Measure 3 = 
VAR __month = MONTH(MAX('Calendar'[Date]))
RETURN
CALCULATE(ALL([Cumulative payments to date]),MONTH('Calendar'[Date])=__month -1 + [Cumulative future forecasts])

    Something like that. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

     

    Also See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

    https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Greg

       

      Thanks for your reply.

       

      I've tried using the formula but I get the following error:

       

      'A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.'

       

      Any ideas what this might means?

       

      Thanks!

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        7 years ago

        That means I seriously screwed up my syntax. I think this is what I was going for:

         

        Measure 3 = 
VAR __month = MONTH(MAX('Calendar'[Date]))
RETURN
CALCULATE([Cumulative payments to date],FILTER(ALL(Calendar),MONTH('Calendar'[Date])=__month -1) + [Cumulative future forecasts])

        Been a long day.