Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am counting YTD MOnth. I have a date Table but all I want is a measure that will enable me to get a value when we are in a certain month. Eg. April =1, May =2 , June =3, July .....March =12. I am using this Value to calculate another measure.
I was using the below formula and it was working fine in December Until I noticed a -2 value instead of 10.
YTD Month = IF(
ISFILTERED('Calendar'[Month]) || ISFILTERED('Calendar'[Date]),
MAX('Calendar'[Financial month Sort]),
DATEDIFF(DATE(YEAR(TODAY()),4,1), TODAY(),MONTH) +1
)
Solved! Go to Solution.
If for some reason your calendar table is not complete (missing dates) you can try using this dax code to create new calendar table.
https://www.dropbox.com/s/to99av1um9o7527/CalendarTableDAX.txt?dl=0
Proud to be a Super User!
Thanks, @ALLUREAN ,it's somewhat complicated hence I need the formula in the format that only returns the month number depending on how far we are in the financial year.
FiscalMonth4 =
VAR _startOfFiscalYear = 4
RETURN
MOD( MONTH(['Calendar'Date])+ (13-_startOfFiscalYear) -1 ,12) +1)
or if not working, if you have month number (1-12) try this:
FisclaMonth_4 =
IF((IF('Calendar'[MonthNo]>4, ('Calendar'[MonthNo])-3,9+('Calendar'[MonthNo])))=13,1,(IF('Calendar'[MonthNo]>4, ('Calendar'[MonthNo])-3,9+('Calendar'[MonthNo]))))
Proud to be a Super User!
There is still an error..The columns are not noticed in the formula..
Hey @ALLUREAN , as you can see, I have updated but it is not picking the formulae of the column name MonthNo, I am not sure of the syntax, but its not working...
You should use this as calculated columm, next to MonthNo
Proud to be a Super User!
Hello, @ALLUREAN I see [Date] as a measure.. and I have it as a Date Table. How do I go about that?
If for some reason your calendar table is not complete (missing dates) you can try using this dax code to create new calendar table.
https://www.dropbox.com/s/to99av1um9o7527/CalendarTableDAX.txt?dl=0
Proud to be a Super User!
I edited the post. You shoud use Date column from Calendar, not a measure
Proud to be a Super User!
Hi, @Datagulf
Try this:
YTD_Fiscal4 =
CALCULATE([Yourmeasure], DATESYTD('Calendar'[Date], "03/31") --Starting 1st of April
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |