March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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.
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
Solved! Go to Solution.
Try with IF:
=IF(MAX(Sheet1[Status]="Terminated",CALCULATE(SUM(Sheet1[WSE]),PREVIOUSMONTH(Sheet1[Report Date])), "-")
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
),
)
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 |
@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
Try with IF:
=IF(MAX(Sheet1[Status]="Terminated",CALCULATE(SUM(Sheet1[WSE]),PREVIOUSMONTH(Sheet1[Report Date])), "-")
@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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
114 | |
76 | |
57 | |
52 | |
44 |
User | Count |
---|---|
168 | |
116 | |
63 | |
57 | |
50 |