Forum Discussion

KW123's avatar
KW123
Icon for Helper V rankHelper V
4 years ago
Solved

IF with a Filter?

Hi,  I have the following DAX to calculate a value for the last day of the previous month.  YTDFunded = CALCULATE( SUMX( '2022 Goals', '2022 Goals'[WWARYTD] ), FILTER( ALL(Dates), ...
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    Hi, 

    For your syntax, it seems there is a ) in the wrong position:

    IF(SELECTEDVALUE ('Dates'[Date]))

    Move the bolded ")" to the very end of your dax.

    As for alternative dax try something with this kind of idea:

    Measure 21 =

    var _lmend = ENDOFMONTH(DATEADD('Calendar'[Date],-1,MONTH)) return

    CALCULATE(sum(Table1[Value]), ALL('Calendar'[Date]),'Calendar'[Date]=_lmend)



ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
4 years ago

Hi,

Place the IF in the beginning of your DAX e.g. IF(SELECTEDVALUE ( Dates[Date] ) ) = 1, BLANK(), [YTDFunded]) 

As a sidenote for last day of a month try using ENDOFMONTH function.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

KW123's avatar
KW123
Icon for Helper V rankHelper V
4 years ago

ValtteriN Thank you for your help! 
What I am I doing incorrectly? With this DAX it is returning an error of 'incorrect syntax'

YTDFunded =
IF(SELECTEDVALUE ('Dates'[Date])) = 1, BLANK(),

CALCULATE(
SUMX(
'2022 Goals',
'2022 Goals'[WWARYTD]
),
 
FILTER(
 
ALL(Dates),
(Dates[Date] = MAXX(Dates, Dates[Last day of previous monthC]
))))
  • KW123's avatar
    KW123
    Icon for Helper V rankHelper V
    4 years ago

    ValtteriN Also thank you for your suggestion with ENDOFMONTH. 
    Is there a different DAX I should be using to get what I need instead with the ENDOFMONTH and the IF?

    • ValtteriN's avatar
      ValtteriN
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi, 

      For your syntax, it seems there is a ) in the wrong position:

      IF(SELECTEDVALUE ('Dates'[Date]))

      Move the bolded ")" to the very end of your dax.

      As for alternative dax try something with this kind of idea:

      Measure 21 =

      var _lmend = ENDOFMONTH(DATEADD('Calendar'[Date],-1,MONTH)) return

      CALCULATE(sum(Table1[Value]), ALL('Calendar'[Date]),'Calendar'[Date]=_lmend)