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)



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)