cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## If statement check against day

Hi Experts,

I have created a column to test for the current month:

IsCurrentMonth =
IF (
YEAR (WS_SCCM[Month] ) = YEAR ( TODAY () )
&& MONTH (WS_SCCM[Month] ) = MONTH ( TODAY ()) -1,
"Yes",
"No"
)

Since this is a monthly report I generally work on it from about the 27th of the month.

The problem in the if statement above is that the calculations differs depending on if I am in the report on the last few days in the month or in the new month.

if in the new month, I need to include the "-1".

What I hoped I could do is create another if statement that says if day > 27 then exlcude the '-1' else include the '-1', i.e. in the new month.

Yes, I am probably approaching the challenge incorrectly, but please forgive me as I make the transition between PowerShell and Power BI, as this is how I have done similar tests in PS. 🙂

Any help will be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: If statement check against day

Hi @aydieno ,

I think it will work and I have changed the date on my computer to 2020/6/28 manually and get this result:

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Highlighted
Super User IX

## Re: If statement check against day

@aydieno , Try like

``````IsCurrentMonth =
Switch ( True ,
day(WS_SCCM[Month]) >27 && eomonth(TODAY (),0) =eomonth(WS_SCCM[Month] ,0) ,"Yes"
day(WS_SCCM[Month]) <=27 && eomonth(TODAY (),-1) =eomonth(WS_SCCM[Month] ,0), "Yes",
"No"
)
``````

You might have do some changes as per need

Proud to be a Super User!

Highlighted
Community Support

## Re: If statement check against day

Hi @aydieno ,

If so, try like this formula:

``````IsCurrentMonth =
IF (
DAY ( 'WS_SCCM'[Month] ) <= 27,
IF (
YEAR ( WS_SCCM[Month] ) = YEAR ( TODAY () )
&& MONTH ( WS_SCCM[Month] )
= MONTH ( TODAY () ) - 1,
"Yes",
"No"
),
"No"
)``````

If not, please kindly let me know.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

## Re: If statement check against day

Hi @v-yingjl ,

That is the expected result if the day is <= 27 and the current month is July.

I am not sure it will provide the expected result if the day is between 28 and 31 (during those days the "-1" must be replaced with "0" or removed).

I have made the changes below which I think will work if the day is between 28 and 31. What do you think?

``IsCurrentMonth = IF ( DAY ( 'WS_SCCM'[Month] ) <= 27, IF ( YEAR ( 'WS_SCCM'[Month] ) = YEAR ( TODAY () ) && MONTH ( 'WS_SCCM'[Month] ) = MONTH ( TODAY () ) - 1, "Yes", "No" ), IF ( YEAR ( 'WS_SCCM'[Month] ) = YEAR ( TODAY () ) && MONTH ( 'WS_SCCM'[Month] ) = MONTH ( TODAY () ) , "Yes", "No" ) )``

It's the syntax of the IF I think I needed help with.
Highlighted
Community Support

## Re: If statement check against day

Hi @aydieno ,

I think it will work and I have changed the date on my computer to 2020/6/28 manually and get this result:

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors