Forum Discussion

AnAnalyst's avatar
AnAnalyst
Helper III
6 years ago

DAX Equivalent to Having clause in SQL

Hello,

 

I am trying to create a measure that will give me the KPI Measure value from the below table:

  1. YTD Plan minus YTD Forecast > 0
  2. Full Year Plan minus Full Year Forecast < 0
  3. It will respect the date slicer

 

In short I want to put the -50 value in a card KPI or other charts, but I need the meausre to respect the project context and the date slicer. 

 

Any help is much apprecited. 

 

Thank you

 

Need Help

2 Replies

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      You can calculate YTD and full year as given below. Based on the requirement you can change the end date

       

      Var _last_year= (max('Date'[Date Filer]))-365
Var   _This_year=year(max('Date'[Date Filer]))
Var _min_last_year= (maxx('Date',STARTOFYEAR(DATEADD('Date'[Date Filer],-12,MONTH))))
Var   _min_This_year=year(max(STARTOFYEAR('Date'[Date Filer])))



Var _last_year= (maxx('Date',ENDOFYEAR(DATEADD('Date'[Date Filer],-12,MONTH))))
Var   _This_year=year(max(ENDOFYEAR('Date'[Date Filer])))
Var _min_last_year= (maxx('Date',STARTOFYEAR(DATEADD('Date'[Date Filer],-12,MONTH))))
Var   _min_This_year=year(max(STARTOFYEAR('Date'[Date Filer])))


Var _last_year_val= CALCULATE(sum(Sales[Sales Amount]),(Sales[Sales Date])<=_last_year && (Sales[Sales Date]) >=_min_last_year)
Var   _This_year_val =CALCULATE(sum(Sales[Sales Amount]),(Sales[Sales Date])<=_This_year && (Sales[Sales Date]) >=_min_This_year)