cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

I want to display the previous month and year with conditions

The following command works fine while adding a new column

 

 

FORMAT(EOMONTH(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())),-1), "MMMM-YYYY")

 

 

But when I try to use it like this 

 

 

Check Empty = 
IF(ISBLANK('August''2020'[LSA]),(IF(ISBLANK('August''2020'[Description of the Change]),"*For the month of "& (FORMAT(EOMONTH(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())),-1), "MMMM-YYYY"))  & ", there were no compliant based Cos."), " "))

 

 

It displays 

 

 

Operator or expression '( )' is not supported in this context.

 

 

What's happeing? And how to remove this?

2 ACCEPTED SOLUTIONS
Super User IV
Super User IV

@R_S-13697 , You can use date table and time intelligence

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.



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!

View solution in original post

Super User III
Super User III

You have a syntax error in it. Daxformatter.com is a great site for formatting your code: 

 

AllisonKennedy_0-1604920942716.png

Not sure if this is what you're looking for: 

 

Check Empty =
IF (
    ISBLANK ( 'August''2020'[LSA] ),
    IF (
        ISBLANK ( 'August''2020'[Description of the Change] ),
        "*For the month of "
            FORMAT (
                EOMONTH ( DATE ( YEAR ( NOW () )MONTH ( NOW () )DAY ( NOW () ) )-1 ),
                "MMMM-YYYY"
            ) & ", there were no compliant based Cos."
    ),
    " "
)


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

4 REPLIES 4
Super User III
Super User III

@R_S-13697 yes, I think there was a missing bracket near the end, though I also removed some of the extra brackets that were unnecessary 


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Super User III
Super User III

You have a syntax error in it. Daxformatter.com is a great site for formatting your code: 

 

AllisonKennedy_0-1604920942716.png

Not sure if this is what you're looking for: 

 

Check Empty =
IF (
    ISBLANK ( 'August''2020'[LSA] ),
    IF (
        ISBLANK ( 'August''2020'[Description of the Change] ),
        "*For the month of "
            FORMAT (
                EOMONTH ( DATE ( YEAR ( NOW () )MONTH ( NOW () )DAY ( NOW () ) )-1 ),
                "MMMM-YYYY"
            ) & ", there were no compliant based Cos."
    ),
    " "
)


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

@AllisonKennedy 
Thanks for the help and providing such a great resource(DAX Formatter). I think I was missing a bracket, right?
Stay Awesome.

Super User IV
Super User IV

@R_S-13697 , You can use date table and time intelligence

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.



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!

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors