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

  • Vvelarde's avatar
    Vvelarde
    10 years ago

    Lukester

     

    Just replace

     

    "Q4" ,"1/1/" & FiscalYear

     

    by

     

    "Q4" ,"1/1/" & (FiscalYear)+1

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.

      • Lukester's avatar
        Lukester
        Frequent Visitor
        10 years ago

        Hi Imke,

        I am very new to coding so it is very difficult for me to understand what you are wanting me to try.

         

        I have followed the directions from the reply above but I am ending with the wrong start_YearMonth dates for my fiscal quarters.

         

        Here are is what my Work Fiscal Quarters should be:

        Q1 2014 - (April 1, 2014 to June 30, 2014)

        Q2 2014 - (July 1, 2014 to September 30, 2014)

        Q3 2014 - (October 1, 2014 to December 31, 2014)

        Q4 2014 - (January 1, 2015 to March 31, 2015)

        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)

         

        However, when I use the formula above, i get the following:

        Q1 2014 - (April 1, 2014 to June 30, 2014)

        Q2 2014 - (July 1, 2014 to September 30, 2014)

        Q3 2014 - (October 1, 2014 to December 31, 2014)

        Q4 2014 - (January 1, 2014 to March 31, 2014)

        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, 2015 to March 31, 2015)

         

        Is there a way to correct this in the formula?

         

  • fansari16's avatar
    fansari16
    Frequent Visitor
    7 years ago

    Hi Luke,

     

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