DAX: Count where row value equals filter context

[greenguy2012]

Hey there,

New to DAX so any help is appreciated. My goal is to understand how many customers cancel in a given sold date. Basically how many items did we sell in a given time period vs how many cancelled in that same period. 

What I'm trying to do is count the number of rows where [Cancel Date] column values equal the Pivot Table Row Context Date [Sold Date] grouped by Month. Ex Grouped by Jan Sold Month, 10 cancelled in same Jan period. 

Needed Pivot Table output:

Sold Month. |  Cancel Count

- Jan.  |   10

- Feb   |  15

- Mar   |  20

Hopefully thats clear. Thanks in advance for your help!

[v-yulgu-msft · Accepted solution]

Hi @greenguy2012,

You can new a calculated column similar to:

Cancel Count =
CALCULATE (
    COUNT ( Table[Cancel Date] ),
    FILTER (
        ALLEXCEPT ( Table, Table[Sold Date].MONTH ),
        [Cancel Date] = [Sold Date]
    )
)

Add [Sold Date].Month and above column into table visual.

Regards,

Yuliana Gu