cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lukester Frequent Visitor
Frequent Visitor

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?

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: Fiscal Quarter text to date help

@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

Super User
Super User

Re: Fiscal Quarter text to date help

@Lukester

 

Just replace

 

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

 

by

 

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




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




9 REPLIES 9
v-haibl-msft Super Contributor
Super Contributor

Re: Fiscal Quarter text to date help

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

Fiscal Quarter text to date help_1.jpg

 

Best Regards,

Herbert

Lukester Frequent Visitor
Frequent Visitor

Re: Fiscal Quarter text to date help

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

Re: Fiscal Quarter text to date help

@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 Super Contributor
Super Contributor

Re: Fiscal Quarter text to date help

@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 Frequent Visitor
Frequent Visitor

Re: Fiscal Quarter text to date help

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.  Testing KPI b.JPGTesting KPI c.JPGTesting KPI.JPG

Super User
Super User

Re: Fiscal Quarter text to date help

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Lukester Frequent Visitor
Frequent Visitor

Re: Fiscal Quarter text to date help

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?

 

Fiscal Quarter PowerBI.JPG

Super User
Super User

Re: Fiscal Quarter text to date help

@Lukester

 

Just replace

 

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

 

by

 

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




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




fansari16 Frequent Visitor
Frequent Visitor

Re: Fiscal Quarter text to date help

Hi Luke,

 

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