Forum Discussion

Junaid11's avatar
Junaid11
Icon for Helper V rankHelper V
3 years ago

Count based on Current quarter and Next Quarter

Hello, I have thre columns as shown in table below. I want to count the status column. Open status should be done based on due date column while closed status should be done on closed date column. I...
Need Help
Rudy_D's avatar
Rudy_D
Icon for Resolver I rankResolver I
3 years ago

Hey !

 

Please try this 🙂 :

 

Current Quarter = QUARTER(TODAY())
Quarter Due Date = QUARTER(SELECTEDVALUE(Table[Due Date]))
Quarter Close Date = QUARTER(SELECTEDVALUE(Table[Close Date]))
 
Current Quarter Count =

CALCULATE(COUNT(Table[Status]), FILTER(Table, (Table[Status] = "Open" && [Quarter Due Date] = [Current Quarter]) || (Table[Status] = "Close" && [Quarter Close Date] = [Current Quarter]) ))
 
Next Quarter Count =

CALCULATE(COUNT(Table[Status]), FILTER(Table, (Table[Status] = "Open" && [Quarter Due Date] = [Current Quarter] + 1) || (Table[Status] = "Close" && [Quarter Close Date] = [Current Quarter] + 1) ))
 
Please find below the result for the current quarter count :).
 

 

Junaid11's avatar
Junaid11
Icon for Helper V rankHelper V
3 years ago

Helo Rudy_D ,
Can you provide the file as I am applying same measures but getting blank. Can you provide the file please?

  • Rudy_D's avatar
    Rudy_D
    Icon for Resolver I rankResolver I
    3 years ago

    Please find my test file here :

     

    Test 

    • Junaid11's avatar
      Junaid11
      Icon for Helper V rankHelper V
      3 years ago

      Hello Rudy_D ,
      I checked but next quarter formula is not getting the correct outcome. 

      • Rudy_D's avatar
        Rudy_D
        Icon for Resolver I rankResolver I
        3 years ago

        I don't understand you expectation. 
        Which are the 2 "close" that you expect for the next quarter ? For me there's only one in your table.