Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Experts,
I have created a column to test for the current month:
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.
Solved! Go to Solution.
Hi @Anonymous ,
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.
Hi @Anonymous ,
Is this your expect result?
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.
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" ) )
Hi @Anonymous ,
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.
@Anonymous , 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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |