topic Re: Days in Current Month in DAX Commands and Tips

Days in Current Month

Anonymous — Fri, 10 Jul 2020 14:33:00 GMT

Hello All,

I want to return the number of days in July and I used the measure below measure. It returns 30 days instead of 31 days.

Days_In_Current_Month = DAY(ENDOFMONTH('Date'[Date]))

Regards

Re: Days in Current Month

AntrikshSharma — Fri, 10 Jul 2020 15:03:59 GMT

If you meant currently selected month then you can use

Days in current month = COUNTROWS ( VALUES ( Dates[Date] ) )

Days in current month = DISTINCTCOUNT( Dates[Date] )

if you meant in July 2020 then

Days in current month = COUNTROWS ( FILTER ( Dates, Dates[Month Number] = MONTH ( TODAY () ) && Dates[Calendar Year Number] = YEAR ( TODAY () ) ) )

Re: Days in Current Month

amitchandak — Fri, 10 Jul 2020 15:39:30 GMT

Anonymous , Try

Days_In_Current_Month = DAY(EOMONTH('Date'[Date],0))

if it is a measure
Days_In_Current_Month = DAY(today(),0))

Re: Days in Current Month

Anonymous — Mon, 13 Jul 2020 11:08:56 GMT

AntrikshSharma

I am looking for a measure that will return the number of days in any current month. July is now the current month so it should return 31 days, Next month is August , when we enter August it shoukd return 31 day as well.

i tried your suggestions, measure number 3 returned blank days. please explain what the year number is suppose to stand for , as i dont have that in my date table.

A snip of my date table below and also the measure below.

thank you

Re: Days in Current Month

AntrikshSharma — Mon, 13 Jul 2020 11:14:58 GMT

Calendar year number is just year like 2020, 2019 etc do you have the dates for 2020 in your table?

Re: Days in Current Month

Anonymous — Mon, 13 Jul 2020 11:30:00 GMT

AntrikshSharma

yes i do.

Re: Days in Current Month

Anonymous — Mon, 13 Jul 2020 12:00:21 GMT

Hi Anonymous ,

The above solution looks correct.

However you can try,

Create a Calculated Column in your Date Table.

YearMonthnumber = FORMAT ( Dates[Date],"YYYYM")

Then create a measure

Days in current Month = VAR _month = MONTH ( TODAY () ) VAR _year = YEAR ( TODAY () ) RETURN COUNTROWS ( FILTER ( ALL ( Dates ), Dates[YearMonthnumber] = CONCATENATE ( _year, _month ) ) )

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

Re: Days in Current Month

Anonymous — Mon, 13 Jul 2020 14:21:58 GMT

Anonymous

this worked correctly.

Thank you