Number of days in a month

DAX DAX Calculations
1

Hi
In report view I’m using a table format and have brought in the Date column from the Dates table.
I’m trying to get the number of days in the month for the each of the dates - i.e. 01/01/20 to 31/01/20 should show 31, 01/02/20 to 29/02/20 should show 29, and so on.
The following DAX formula is returning 365 for all dates
= CALCULATE( COUNTROWS( Dates ), all ( Dates[Date] ), VALUE( Dates[MonthInCalendar] ) )
MonthInCalendar has values Jan 2020, Feb 2020, etc
Could I please get some help?
Thanks
Manoj

2

@M_K,

Welcome to the forum – great to have you here!

So close – you were just off by one letter. Change VALUE to VALUES, and it works just fine:

Days in Month = 
CALCULATE( 
    COUNTROWS( Dates ), 
    ALL ( Dates[Date] ), 
    VALUES(Dates[Month & Year] )
)

image

– Brian
eDNA forum – days in month solution.pbix (529.0 KB)

3

Thanks Brian for the quick response :smiley: