Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Datagulf
Responsive Resident
Responsive Resident

Count YTD month and return an Integer with Financial Year starting April

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
)



1 ACCEPTED 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

 

 




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


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




View solution in original post

9 REPLIES 9
Datagulf
Responsive Resident
Responsive Resident

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]))))




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


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




Datagulf
Responsive Resident
Responsive Resident

There is still an error..The columns are not noticed in the formula..

Datagulf
Responsive Resident
Responsive Resident

Datagulf_0-1643201275159.pngDatagulf_1-1643201350622.png

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




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


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




Datagulf
Responsive Resident
Responsive Resident

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

 

 




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


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




I edited the post. You shoud use Date column from Calendar, not a measure




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


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




ALLUREAN
Solution Sage
Solution Sage

Hi, @Datagulf 

Try this:
YTD_Fiscal4 =
CALCULATE([Yourmeasure], DATESYTD('Calendar'[Date], "03/31") --Starting 1st of April




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


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.