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

Calculated Formula to count records within own month

I'm trying to count the number of records within a month and put it in a calculated column so I can use it in another column based calculation.

 

Basically, I need to get the count like this:

 

 Capture.PNG

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@Anonymous

 

Hello, try with this in a calculated column

 

Recordsinthemonth = 
VAR MonthinRow=MONTH(Table1[Date])
RETURN
COUNTROWS(FILTER(All(Table1),MONTH(Table1[Date])=MonthinRow))

Regards

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

3 REPLIES 3
Vvelarde
Community Champion
Community Champion

@Anonymous

 

Hello, try with this in a calculated column

 

Recordsinthemonth = 
VAR MonthinRow=MONTH(Table1[Date])
RETURN
COUNTROWS(FILTER(All(Table1),MONTH(Table1[Date])=MonthinRow))

Regards

 

Victor

Lima - Peru




Lima - Peru
Anonymous
Not applicable

That worked so easily. I just started learning DAX and all the solutions I could think of were within its own bounds, ironically, like my problem here.

Hello,
Since you just started learning DAX it's a good idea to be able to think about different solutions to your problems. In case if you are in that pre-variables DAX environment  SSAS 2012 or Excel < 2016 here is the solution to question with EARLIER()

 

Count =
COUNTROWS(
    FILTER(
        Table1,
        MONTH(Table1[Date]) = MONTH(EARLIER(Table1[Date]))
    )
)

Nick -

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.