Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Aggregate per latest date of each month

Hello,

 

Sans titre.pngI 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])
)

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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.

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Employee
Employee

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.

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.