Forum Discussion

mdaamirkhan's avatar
mdaamirkhan
Icon for Post Prodigy rankPost Prodigy
5 years ago

Current year +1 now working

Hi 

Current year +1 will be based on the Input_Contract_Report[End Date].

 

I need help in the following dax which is not working. if you can help me and provide the dax

Estimated revenue Current year +1 =
VAR thisYear = YEAR ( TODAY()) +1
VAR val =
CALCULATE (
[Number of service month_2], -- COUNTA ( Input_Contract_Report[months] ),
FILTER ( Input_Contract_Report, Input_Contract_Report[Active] = "Yes" ),
FILTER (
Input_Contract_Report,
Input_Contract_Report[End Date].[Year] = thisYear
),
ALLEXCEPT('Input_Contract_Report','Input_Contract_Report'[Column])
)
RETURN
 
ABS( val * [Average revenue])

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    mdaamirkhan , I usually prefer time intelligence with date table

     

    example

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    next YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    next year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],1,Year)),"12/31"))

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    https://www.youtube.com/watch?v=km41KfM_0uA

    • mdaamirkhan's avatar
      mdaamirkhan
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Please provide me accordign my dax query 


      Estimated revenue Current year +1 =
      VAR thisYear = YEAR ( TODAY()) +1
      VAR val =
      CALCULATE (
      [Number of service month_2], -- COUNTA ( Input_Contract_Report[months] ),
      FILTER ( Input_Contract_Report, Input_Contract_Report[Active] = "Yes" ),
      FILTER (
      Input_Contract_Report,
      Input_Contract_Report[End Date].[Year] = thisYear
      ),
      ALLEXCEPT('Input_Contract_Report','Input_Contract_Report'[Column])
      )
      RETURN