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?
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] ) )
eDNA forum – days in month solution.pbix (529.0 KB)
Thanks Brian for the quick response