cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lukester
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
v-haibl-msft
Microsoft
Microsoft

@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

View solution in original post

Vvelarde
Community Champion
Community Champion

@Lukester

 

Just replace

 

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

 

by

 

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




Lima - Peru

View solution in original post

9 REPLIES 9
v-haibl-msft
Microsoft
Microsoft

@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

View solution in original post

Hi Luke,

 

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

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

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Lukester
Frequent Visitor

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

Vvelarde
Community Champion
Community Champion

@Lukester

 

Just replace

 

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

 

by

 

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




Lima - Peru

View solution in original post

v-haibl-msft
Microsoft
Microsoft

@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

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

 

 

 

@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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors