Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
KW123
Helper V
Helper V

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


1 ACCEPTED SOLUTION

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)







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
KW123
Helper V
Helper V

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

ValtteriN
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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]
))))

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

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)







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors