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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

PREVIOUSMONTH & FITLER

Hi, 

 

I am trying to report a previous month's number but only when another column is filtered. i would like the model to report the previous month count for when it is terminated.  i have tried the following formula but no luck.

 

Term Count = CALCULATE(SUM(Sheet1[WSE]),PREVIOUSMONTH(Sheet1[Report Date]),filter(Sheet1,Sheet1[Status]="Terminated"))

 

mrandyhoward_0-1598041759558.png

 

If i remove the last filter i can get the previous month count, but i only want the value for when the status is terminated.

 

Any ideas?

 

thanks

 

1 ACCEPTED SOLUTION
Tahreem24
Super User
Super User

Try with IF:

=IF(MAX(Sheet1[Status]="Terminated",CALCULATE(SUM(Sheet1[WSE]),PREVIOUSMONTH(Sheet1[Report Date])), "-")

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

View solution in original post

4 REPLIES 4
pranit828
Community Champion
Community Champion

Hi @Anonymous 

I would use the below formula and tweak it as required.

    CALCULATE (
        SUM(Sheet1[WSE]),
        FILTER (
            ALLexcept(PREVIOUSMONTH(Sheet1[Report Date])),
            Sheet1[Status]=="Terminated"    // The == operator distinguishes between blank and 0/empty string
        ),
    )




PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
Anonymous
Not applicable

@pranit828  thanks for your help but i received the below error when using the ALLEXCEPT function

"The ALLEXCEPT function expects a table reference for argument 2, but a table expression was used"

 

i tried a few variations of moving the PREVIOUSMONTH function but no luck

Tahreem24
Super User
Super User

Try with IF:

=IF(MAX(Sheet1[Status]="Terminated",CALCULATE(SUM(Sheet1[WSE]),PREVIOUSMONTH(Sheet1[Report Date])), "-")

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Anonymous
Not applicable

@Tahreem24 Thank you for your assistance, unfortunately i received the below error when trying the Max function

"The MAX function only accepts a column reference as an argument"

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.