Aggregate per latest date of each month

[Anonymous]

Hello,

I want to create a measure to count non-empty values of MyColumn for the latest date of each month. "Update date" is linked to a Date table.

In my example here, I want to count the values of 27/02 for February and 30/03 for March, so the expected result is:

February   2

March       4

So far I could only get the count of the last date of the table (30/03) with this formula, but not last date of each month:

My measure = 
CALCULATE(
    COUNT([MyColumn]);
    FILTER('My table'; <my filters>);
    LASTDATE('[Update date]);
    USERELATIONSHIP('My table'[Update date]; 'Date'[Date])
)

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please try this measure:

My measure =
CALCULATE (
    COUNT ( 'My table'[MyColumn] ),
    FILTER (
        ALL ( 'My table' ),
        'My table'[Update date]
            = CALCULATE (
                MAX ( 'My table'[Update date] ),
                FILTER (
                    ALL ( 'My table' ),
                    MONTH ( 'My table'[Update date] ) = MONTH ( MAX ( 'My table'[Update date] ) )
                        && 'My table'[MyColumn] <> BLANK ()
                )
            )
    )
)

Add [Date] column from Date table to visual.

Best regards,

Yuliana Gu

[Anonymous]

Great, thanks! I couldn't have done it by myself...

[Greg_Deckler]

I don't even see February 2nd and March 4 in your data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

I edited my question, I hope it's clearer now. It's not Feb. 2nd and March 4th but 2 non-empty rows for Feb. and 4 non-empty rows for March.