Countrows last quarter in a matrix

[Penn]

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], 😎 & 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

[Penn]

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

[dax · Accepted solution]

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.

[Penn]

Good idea!