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

Count row by year and month

Hello everyone.
I'm still new to PowerBI, and I know somebody might have already posted regarding this topic, but I'm still confused.

Please help me with this.

My dataset contains NC ID and NC Open Date (from 2021 to 2022). Now, I want to know how many NCs are opened each month and save the total number like below. So that I can union all the metrics to a monthly metrics table. 

 2021Jan2021Feb... 2022Aug
NC Opened MTD1015... 0

 

When I apply the formula, it's all 1, how can I get the total? I have a calendar table, but I don't know how to use it. Please help, 

thanks in advance!

Lucasw_0-1659970184617.png

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I figure out the result
NC Opened MTD = CALCULATE(COUNTROWS('NCs Opened MTD'),
FILTER('NCs Opened MTD',
        MONTH ( 'NCs Opened MTD'[NC Date Opened] ) = MONTH ( EARLIER ( 'NCs Opened MTD'[NC Date Opened] ) )
        && YEAR('NCs Opened MTD'[NC Date Opened] ) = YEAR(  EARLIER ( 'NCs Opened MTD'[NC Date Opened] ))))

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

I figure out the result
NC Opened MTD = CALCULATE(COUNTROWS('NCs Opened MTD'),
FILTER('NCs Opened MTD',
        MONTH ( 'NCs Opened MTD'[NC Date Opened] ) = MONTH ( EARLIER ( 'NCs Opened MTD'[NC Date Opened] ) )
        && YEAR('NCs Opened MTD'[NC Date Opened] ) = YEAR(  EARLIER ( 'NCs Opened MTD'[NC Date Opened] ))))
amitchandak
Super User
Super User

@Anonymous , Create a date only column

 

date = datevalue([NC Open Date])

 

Join it with table having month, etc

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])

)

 

Plot with help from date table

 

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.