Forum Discussion

Tlotly's avatar
Tlotly
Icon for Helper V rankHelper V
5 years ago
Solved

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.

 

 

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

 

Need Help
  • Kumail's avatar
    Kumail
    5 years ago

    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

6 Replies

  • Kumail's avatar
    Kumail
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

    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's avatar
        Kumail
        Icon for Impactful Individual rankImpactful Individual
        5 years ago

        Tlotly 

        You're Welcome!!

         

        If you liked the solution, dont forget to Thumbs up

         

        Thanks,

        Kumail Raza

         

    • Tlotly's avatar
      Tlotly
      Icon for Helper V rankHelper V
      5 years ago

      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. 

  • Kumail's avatar
    Kumail
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

    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