Forum Discussion

Lukester's avatar
Lukester
Frequent Visitor
10 years ago
Solved

Fiscal Quarter text to date help

Need help!!!! I am working with excel workbook that I have imported into PowerBI and in the table, there is a column called Fiscal Quarter. The values are formatted as text like "2015 Q1", "2014 Q4". I want to be able to change the values so they are recognized in PowerBI as dates so I can do some previous quarter comparisons.  Does anyone have any tips or ideas?

 

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    Lukester

     

    If you have different Fiscal Quarters, you can just replace the actual start and end dates in provided formulas.
    Just to confirm, do you have any other questions have not been solved?

     

    Best Regards,
    Herbert

9 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Lukester

     

    In this scenario, you can create another two columns in Power BI Desktop which identify the start and end date of this quarter with following formulas. Then change “Data Type” of these two columns to “Date”.

     

    Start_YearMonth = 
VAR FiscalYear =
    LEFT ( Table1[Fiscal Quarter], 4 )
RETURN
    (
        SWITCH (
            RIGHT ( Table1[Fiscal Quarter], 2 ),
            "Q1", "1/1/" & FiscalYear,
            "Q2", "4/1/" & FiscalYear,
            "Q3", "7/1/" & FiscalYear,
            "Q4", "10/1/" & FiscalYear
        )
)
    End_YearMonth = 
VAR FiscalYear =
    LEFT ( Table1[Fiscal Quarter], 4 )
RETURN
    (
        SWITCH (
            RIGHT ( Table1[Fiscal Quarter], 2 ),
            "Q1", "3/31/" & FiscalYear,
            "Q2", "6/30/" & FiscalYear,
            "Q3", "9/30/" & FiscalYear,
            "Q4", "12/31/" & FiscalYear
        )
)

     

    Best Regards,

    Herbert

    • Lukester's avatar
      Lukester
      Frequent Visitor
      10 years ago

      Hi Herbert,

      Thanks for replying to my question.

       

      I did add the two columns and added the calculations. Those worked but I forgot to mention that our Fiscal Quarters have different start and end dates. 

       

      Example:

       

      Q1 2015 = April 1, 2015 to June 30, 2015

      Q2 2015 = July 1, 2015 to September 30, 2015

      Q3 2015 - October 1, 2015 to December 31, 2015

      Q4 2015 = January 1, 2016 to March 31, 2016

      Q1 2016 = April 1, 2016 to June 30, 2016

       

       

       

      • v-haibl-msft's avatar
        v-haibl-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        Lukester

         

        If you have different Fiscal Quarters, you can just replace the actual start and end dates in provided formulas.

        Just to confirm, do you have any other questions have not been solved?

         

        Best Regards,

        Herbert

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Lukester

     

    If you have different Fiscal Quarters, you can just replace the actual start and end dates in provided formulas.
    Just to confirm, do you have any other questions have not been solved?

     

    Best Regards,
    Herbert

    • Lukester's avatar
      Lukester
      Frequent Visitor
      10 years ago

      Ultimately, What I am trying to do is to use the KPI to show if the current (%) value of the quality indicator of the Fiscal quarter has gone up or down compared to the previous fiscal quarter. 

      • ImkeF's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion
        10 years ago

        If you end up using DAX Time intelligence functions anyway, you might prefer to go the other way around and create a calenadar-table instead where you add your specific Quarter-columns according to your business logic. Then connect both tables in the data model and let the filter propagation do it's job.

         

        In order to avoid many-2-many-relations here, I'd recommend to create an additional column (StartDateQuarter) that is of date-format in the query-editor.

    • fansari16's avatar
      fansari16
      Frequent Visitor
      7 years ago

      Hi Luke,

       

      Can you look my post. I have some problems too.