Forum Discussion

Penn's avatar
Penn
Icon for Resolver I rankResolver I
7 years ago
Solved

Countrows last quarter in a matrix

Hi all,

 

I have a table with two text columns [Quarter] and [ID]. How I want to return the count of [ID] last quarter as below.

 

Here are the calculations used in the matrix.

Measure: 

# of Opportunities = CALCULATE(COUNT(Opportunities[Id]))
 
Calculated Column:
Last Quarter = IF(VALUE(RIGHT(Opportunities[Quarter], 1)) = 1, VALUE(LEFT('Opportunities'[Quarter], 4) - 1) & " / Q4", LEFT('Opportunities'[Quarter], 8) & VALUE(RIGHT('Opportunities'[Quarter], 1) - 1))
 
Measure: (Not Working)
Number of Opportunities Last Quarter = CALCULATE(COUNT('Opportunities'[Id]), FILTER('Opportunities', Opportunities[Quarter] = [Last Quarter]))
 Is there anyway to make this happen without using a date table? Thanks
 

 

Need Help
  • dax's avatar
    dax
    7 years ago

    Hi Penn,

    You could try to create a column like below(order quarter ace)

    Then create a measure like beow

    last q =
CALCULATE (
    COUNT ( 'LAST QUARTRE'[ID] ),
    FILTER (
        ALL ( 'LAST QUARTRE' ),
        'LAST QUARTRE'[Custom]
            = MIN ( 'LAST QUARTRE'[Custom] ) - 1
    )
)

    Best Regards,

    Zoe Zhi

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Penn's avatar
    Penn
    Icon for Resolver I rankResolver I
    7 years ago

    I think I better build a date table then...problem solved

    • dax's avatar
      dax
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi Penn,

      You could try to create a column like below(order quarter ace)

      Then create a measure like beow

      last q =
CALCULATE (
    COUNT ( 'LAST QUARTRE'[ID] ),
    FILTER (
        ALL ( 'LAST QUARTRE' ),
        'LAST QUARTRE'[Custom]
            = MIN ( 'LAST QUARTRE'[Custom] ) - 1
    )
)

      Best Regards,

      Zoe Zhi

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.