Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 t...
  • Tahreem24's avatar
    Tahreem24
    6 years ago

    Try with IF:

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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous  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