topic Re: IF with a Filter? in DAX Commands and Tips

IF with a Filter?

KW123 — Thu, 11 Aug 2022 19:08:57 GMT

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

Re: IF with a Filter?

ValtteriN — Thu, 11 Aug 2022 19:32:22 GMT

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/

Re: IF with a Filter?

KW123 — Thu, 11 Aug 2022 19:40:47 GMT

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]

))))

Re: IF with a Filter?

KW123 — Thu, 11 Aug 2022 19:45:13 GMT

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?

Re: IF with a Filter?

ValtteriN — Thu, 11 Aug 2022 20:01:47 GMT

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)

Re: IF with a Filter?

KW123 — Thu, 11 Aug 2022 20:05:04 GMT

ValtteriN That worked!! Thank you very much for your help