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 cont...
Need Help
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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks Greg, now i get this error:

     

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

     

    Any ideas?

     

    Thanks for your help so far!

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      7 years ago

      Shoot, paren in the wrong spot! Syntax...

       

      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]

      This is what happens when I don't actually test my DAX code in Power BI and try to wing it.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks again for your reply.

         

        Unfortunately that measure basically re-creates a line the same as the existing measure for Cumulative Forecasts.

         

        I was hoping to get a line that starts from July thats show cumulative payments to date. For example, as at 31 December, the total cumulative figure for the first 6 months would be $1,000,000. The line would then change to Cumulative Forecasts where the January figure would be the $1,000,000 paid to date plus January forecasts. This would then continue on until 30 June.