Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I need a formula that counts the number of months from a certain date, I have searched and count not find this exactly. I need to be able to count the months in the FY and our FY starts in September.
Basically need a formula that is TODAY - [09/01/2019] = 11
then in August it would equal 12 and so on.
Thanks in advance
Solved! Go to Solution.
Hey @Anonymous , Try this:
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
CALENDAR(
DATE( 2019, 9, 1 ),
TODAY()
),
"Months", YEAR( [Date] ) * 100
+ MONTH( [Date] )
)
)
)
You should, or course, replace the DATE(2019,9,1) with something a bit more dynamic like the start of your current fiscal year.
What this does is:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHey @Anonymous , Try this:
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
CALENDAR(
DATE( 2019, 9, 1 ),
TODAY()
),
"Months", YEAR( [Date] ) * 100
+ MONTH( [Date] )
)
)
)
You should, or course, replace the DATE(2019,9,1) with something a bit more dynamic like the start of your current fiscal year.
What this does is:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingWorks perfect, thank you!
Great. Glad it helped @Anonymous
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@Anonymous
I'm assuming you have a date table with a month-year field so it would be something like this.
Month Count =
VAR _Today = TODAY ()
VAR _PYStart = DATE ( YEAR ( _Today ) - IF ( MONTH ( _Today ) >= 9, 0, 1), 9, 1 )
RETURN
COUNTROWS (
CALCULATETABLE (
VALUES ( DATES[Month Year] ),
DATES[Date] <= _Today && DATES[Date] >= _PYStart
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |