Calculate a conditional sum per month

[Anonymous]

Hello,

I have a table with a [Date] field and the following measure:

Monthly_sum=
CALCULATE (
COUNTROWS ( 'History_table' ),
FILTER (
'History_table',
CONTAINSSTRING(([Closure Strategy]), "CAT 1") || CONTAINSSTRING(([Closure Strategy]), "CAT 2")
)
)

On X axis I put [Date] and values are the measure above. I want to count the number of [id] per month that meet the criteria above.

The problem is that the measure it is not broken down by each month but it is a total that is displayed for all months (the same value)

Please advise.

Regards

[v-yangliu-msft]

Hi  @Anonymous ,

According to your description, I create this data:

Here are the steps you can follow：

1. Create measure.

Measure =
CALCULATE(
    COUNTROWS('History_table'),
FILTER(ALL('History_table'),
    OR(
        CONTAINSSTRING('History_table'[Closure Strategy],"CAT 1"),
        CONTAINSSTRING('History_table'[Closure Strategy],"CAT 2"))
        &&
        'History_table'[Year]=MAX('History_table'[Year])
        &&
        'History_table'[month]=MAX('History_table'[month])))

2. Result:

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[lbendlin]

Monthly_sum=
CALCULATE (
COUNTROWS ( 'History_table' ),

ALLEXCEPT(DatesTable,DatesTable[Month]),
FILTER (
'History_table',
CONTAINSSTRING(([Closure Strategy]), "CAT 1") || CONTAINSSTRING(([Closure Strategy]), "CAT 2")
)
)

[Anonymous · Accepted solution]

Thank you Ibendlin, I am revising the answer, it WORKS ! I made a mistake in regards of filters.

Marked as solution!