Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
)
)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |