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),
(Dates[Date] = MAXX(Dates, Dates[Last day of previous monthC]
))))

However I am trying to put and IF clause that makes the values for just January blank.  
IF MONTH ( SELECTEDVALUE ( Dates[Date] ) ) = 1,
Blank(),

I just don't know where in the above DAX to put it as it keeps returning an error. 

Thanks


  • 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)



5 Replies

  • 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?