cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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
Community Champion
Community Champion

@fmx

 

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
Community Champion
Community Champion

@fmx

 

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

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.

Continued Contributor
Continued Contributor

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
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors