Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
Just replace
"Q4" ,"1/1/" & FiscalYear
by
"Q4" ,"1/1/" & (FiscalYear)+1
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
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.
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
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?
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |