PREVIOUSMONTH & FITLER

[Anonymous]

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"))

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

[pranit828]

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
        ),
    )

[Anonymous]

@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 · Accepted solution]

Try with IF:

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

[Anonymous]

@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"