March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
I need a measure to count the number of days in the current month, without the use of any tables etc.
it should show whether this month has either 30, 31, 28 or 29 days. I want to use this number to calculate daily target from a fixed taraget value.
Thank You!
Solved! Go to Solution.
HI @Anonymous
You could try
Measure 2 = VAR t = TODAY() RETURN COUNTROWS( CALENDAR( FORMAT(t,"YYYY-MM-01") , EDATE(FORMAT(t,"YYYY-MM-01"),1)-1 ) )
or
Measure 2 = VAR s = FORMAT(TODAY(),"YYYY-MM-01") VAR e = EDATE(FORMAT(TODAY(),"YYYY-MM-01"),1) RETURN int( e-s)
HI @Anonymous
You could try
Measure 2 = VAR t = TODAY() RETURN COUNTROWS( CALENDAR( FORMAT(t,"YYYY-MM-01") , EDATE(FORMAT(t,"YYYY-MM-01"),1)-1 ) )
or
Measure 2 = VAR s = FORMAT(TODAY(),"YYYY-MM-01") VAR e = EDATE(FORMAT(TODAY(),"YYYY-MM-01"),1) RETURN int( e-s)
Hi @powerbiuser32,
Try this measure:
Days_current_month = DAY ( IF ( MONTH ( TODAY () ) = 12; DATE ( YEAR ( TODAY () ) + 1; 1; 1 ); DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) + 1; 1 ) ) - 1 )
Regards.
Hi @Anonymous
Thank you. However the code returns a syntax error when copied an pasted.
Regards.
The code works, surely what is happening is that you have Power BI Desktop configured to use commas instead of semicolons as DAX separators, and for this reason @Phil_Seamark's code worked without problems (using commas). So you should simply change the semicolons by commas.
Here you have a tool to detect syntax errors in DAX and format with a recommended indentation, you can modify the separators in US / UK (comma) and Others (semicolon):
https://www.daxformatter.com
Regards.
User | Count |
---|---|
118 | |
77 | |
58 | |
52 | |
46 |
User | Count |
---|---|
171 | |
117 | |
63 | |
57 | |
51 |