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
Tlotly
Resolver II
Resolver II

Create a mmm-yyyy column

Dear community

 

Is it possible for a YearMont column to be a date format? So basically I'm trying to create a financial yearmonth field from the calendar but I'm not getting it right. It creates successfully as below but I'm struggling to convert it to a date format so that I create the column with this format  "mmm-yyyy", e.g. Jan - 2020.

Tlotly_0-1625041158818.png

 

 

Below is the DAX I used to create the column,  I even used the Format function and I'm still not getting the results I want. I've tried changing the column to Text then Date, it gives me an error. Since this is from a calendar created within the report, I can't use Transform data option. 

 

Please assist.

FinYearMonth = VAR FiscalStartMonth = 7
                            VAR FinYearMonth =
                       IF ( MONTH ( [Date] ) > ( FiscalStartMonth - 1 ),
                         ((YEAR ( [Date] ) * 100)+1) + MONTH([Date]),
                           ((YEAR ( [Date] ) * 100)) + MONTH([Date]))
)
RETURN
FinYearMonth

Thank you

Matlotlo

 

1 ACCEPTED SOLUTION
Kumail
Post Prodigy
Post Prodigy

@Tlotly 

 

If I understood your logic correctly, then this should help

 

 

FinYearMonth = 
VAR FiscalStartMonth = 7
VAR FinYearMonth = If( MONTH([Date]) > (FiscalStartMonth - 1),
                        DATE((YEAR([Date])+1),MONTH([Date]),DAY([Date])),
                        DATE(YEAR([Date]),MONTH([Date]),DAY([Date])))
RETURN
FORMAT(FinYearMonth, "mmmm-yyyy")

 

 

 

I hope this helps.

 

Regards

Kumail Raza

View solution in original post

6 REPLIES 6
Kumail
Post Prodigy
Post Prodigy

@Tlotly 

 

If I understood your logic correctly, then this should help

 

 

FinYearMonth = 
VAR FiscalStartMonth = 7
VAR FinYearMonth = If( MONTH([Date]) > (FiscalStartMonth - 1),
                        DATE((YEAR([Date])+1),MONTH([Date]),DAY([Date])),
                        DATE(YEAR([Date]),MONTH([Date]),DAY([Date])))
RETURN
FORMAT(FinYearMonth, "mmmm-yyyy")

 

 

 

I hope this helps.

 

Regards

Kumail Raza

@Kumail  thank you very much. Worked liked a charm

@Tlotly 

You're Welcome!!

 

If you liked the solution, dont forget to Thumbs up

 

Thanks,

Kumail Raza

 

Kumail
Post Prodigy
Post Prodigy

Hello @Tlotly 

 

If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.

 

Regards
Kumail Raza

amitchandak
Super User
Super User

@Tlotly , if you have date , you can do it using format

 

format([Date], "mmm -yyyy")

@amitchandak  the only date I have is a calendar date, hence I'm trying to create Fiscal date but with mmm-yyyy format. My fiscal dates are from Jul - Jun. 

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.