cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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:

june.png

 

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.

View solution in original post

4 REPLIES 4
Highlighted
Super User IX
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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: If statement check against day

Hi @aydieno ,

Is this your expect result?

month re.png

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
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:

june.png

 

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.

View solution in original post

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

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.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors