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"....
Need Help
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