Create a mmm-yyyy column

[Tlotly]

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.

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

[Kumail · Accepted solution]

@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

[Tlotly]

@Kumail  thank you very much. Worked liked a charm

[Kumail]

@Tlotly 

You're Welcome!!

If you liked the solution, dont forget to Thumbs up

Thanks,

Kumail Raza

[Kumail]

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]

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

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

[Tlotly]

@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.