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

Formula to count # of dates per month in a date column

CaptureDate22.PNG

I have a date column with multiple dates, i want to count the number of dates per month in a date column. For example, if there are ten dates for january 2020, then we should have 10  next to each january dates. Thanks

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

You could use EARLIER Function to create a calculated column as below:

https://docs.microsoft.com/en-us/dax/earlier-function-dax

https://powerpivotpro.com/2012/03/the-correct-usage-of-earlier/

Column =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Date] ),
    FILTER ( 'Table', 'Table'[Year] = EARLIER ( 'Table'[Year] ) )
)

 

Regards,

Lin

Community Support Team _ Lin
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

3 REPLIES 3
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

You could use EARLIER Function to create a calculated column as below:

https://docs.microsoft.com/en-us/dax/earlier-function-dax

https://powerpivotpro.com/2012/03/the-correct-usage-of-earlier/

Column =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Date] ),
    FILTER ( 'Table', 'Table'[Year] = EARLIER ( 'Table'[Year] ) )
)

 

Regards,

Lin

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

Hi,

 

Try creating a measure using something like below

Measure = 
CALCULATE(
    DISTINCTCOUNT('Table'[Date]),
    FILTER(ALL('Table'),
    'Table'[Year] = MAX('Table'[Year])
    ))

 

 

Anonymous
Not applicable

@Gordonlilj  Thanks for responding, but i want the counts to differ by each monthYear([Year] Column)

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.