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

 

  • Tahreem24's avatar
    Tahreem24
    6 years ago

    Try with IF:

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

     

4 Replies

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    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

      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"

  • 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