Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Forecasting Future Numbers - table

Circling back on this, I'm trying to put into a table the financial forecast for the next 5 years, based on this year's growth rate. I have precalculated everything and have this DAX:

Measure 2 = IF(HASONEVALUE('Calendar Table' [YEAR DATE]), VALUES(('Calendar Table'[YEAR DATE])) > VALUE(YEAR(TODAY())),  
CALCULATE([Sales], FILTER('Sales Report','Sales Report'[Sale Date]=YEAR(TODAY())))
* POWER((1+[Current Growth%]), 5))
 
This is what I'm getting - T/F instead of the calculation for results: 
 Do I need a DATEADD in my DAX to get the sales numbers?
Need Help

2 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    Can you check the criteria in your measure? It looks like you are evaluting if the table row has one [YEAR DATE] value which is true. Then you are evaluting whether or not the [YEAR DATE] value in the row is greater than the current year. This evaluates as true in all rows shown in your table, which is why you are seeing true returned.
    If you change your criteria to 

    HASONEVALUE('Calendar Table' [YEAR DATE]) && VALUES(('Calendar Table'[YEAR DATE])) > VALUE(YEAR(TODAY()))

    does that get you the result you are looking for?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    thanks jgeddes, here is what I put in and here is the error I recieved:

     

    Measure 2 = IF(HASONEVALUE('Calendar Table' [YEAR DATE]) && VALUES(('Calendar Table'[YEAR DATE])) > VALUE(YEAR(TODAY())),    
    CALCULATE([Sales], FILTER('Sales Report','Sales Report'[Sale Date]=YEAR(TODAY())))
    * POWER((1+[Current Growth%]), 5))
     

    Error: Caclulation error in mesaure 'Extrap Tabl'[Measure 3]: A table of multiple values was supplied where a single value was expected.