Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone!
I have a calendar table in Power Query and I need to work out how to get the relative Fiscal Quarter Offset. I understand I need the current date to extract the current fiscal year but I can't work out the m code syntax.
I was able to build something similar in Excel function, but there is an issue when today's date is in the 2nd month of the quarter
Excel =FLOOR(((YEAR(Target_Date)-YEAR(Initial_Date))*12+MONTH(Target_Date)-MONTH(Initial_Date))/3, 1)
Desired output in red:
Year | Month | Fiscal Month | Fiscal Quarter | Desired Output: Qtr Offset |
2018 | Nov | 1 | 1 | -5 |
2018 | Dec | 2 | 1 | -5 |
2019 | Jan | 3 | 1 | -5 |
2019 | Feb | 4 | 2 | -4 |
2019 | Mar | 5 | 2 | -4 |
2019 | Apr | 6 | 2 | -4 |
2019 | May | 7 | 3 | -3 |
2019 | Jun | 8 | 3 | -3 |
2019 | Jul | 9 | 3 | -3 |
2019 | Aug | 10 | 4 | -2 |
2019 | Sep | 11 | 4 | -2 |
2019 | Oct | 12 | 4 | -2 |
2019 | Nov | 1 | 1 | -1 |
2019 | Dec | 2 | 1 | -1 |
2020 | Jan | 3 | 1 | -1 |
2020 | Feb | 4 | 2 | 0 |
2020 | Mar | 5 | 2 | Now ---> 0 |
2020 | Apr | 6 | 2 | 0 |
2020 | May | 7 | 3 | 1 |
2020 | Jun | 8 | 3 | 1 |
2020 | Jul | 9 | 3 | 1 |
2020 | Aug | 10 | 4 | 2 |
2020 | Sep | 11 | 4 | 2 |
2020 | Oct | 12 | 4 | 2 |
Solved! Go to Solution.
Hi @Anonymous ,
You also could refer to below M code to see whteher it work or not.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddA9D4IwEIDhv0I606QtH8JIYhxMxMERGZA0LkaIEY3/3iup9q6UgSPkAXp5m4YpIQsWs3p4wZT24hlr479tdQ9TLayEp313h5kEbacvMFP7LU+xHboHzCxo1WgsX/nuA3Njz+QJ2WUyuxQrdoNZBq2armZ/YZflCuNJjwZlEI/906AKopdUYvOS/kyJZVJiNKnARIvWwzvinJ8nIRIdkRdpXu8fuC7disT1CLcl5KVV2LyyxLywxFzXbD4SkctKSJKqC3JR8/mOyEW11H4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t, #"Fiscal Month" = _t, #"Fiscal Quarter" = _t, #"Desired Output: Qtr Offset" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", type text}, {"Fiscal Month", Int64.Type}, {"Fiscal Quarter", Int64.Type}, {"Desired Output: Qtr Offset", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each Number.ToText([Year]) &" "& [Month]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom.1", type date}}),
#"Added Custom6" = Table.AddColumn(#"Changed Type1", "Custom.5", each Date.QuarterOfYear(Date.AddMonths([Custom.1],2))-
Date.QuarterOfYear(Date.AddMonths(DateTime.LocalNow(),2))+
(Date.Year(Date.AddMonths([Custom.1],2))-Date.Year(Date.AddMonths(DateTime.LocalNow(),2)))*4)
in
#"Added Custom6"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You also could refer to below M code to see whteher it work or not.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddA9D4IwEIDhv0I606QtH8JIYhxMxMERGZA0LkaIEY3/3iup9q6UgSPkAXp5m4YpIQsWs3p4wZT24hlr479tdQ9TLayEp313h5kEbacvMFP7LU+xHboHzCxo1WgsX/nuA3Njz+QJ2WUyuxQrdoNZBq2armZ/YZflCuNJjwZlEI/906AKopdUYvOS/kyJZVJiNKnARIvWwzvinJ8nIRIdkRdpXu8fuC7disT1CLcl5KVV2LyyxLywxFzXbD4SkctKSJKqC3JR8/mOyEW11H4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t, #"Fiscal Month" = _t, #"Fiscal Quarter" = _t, #"Desired Output: Qtr Offset" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", type text}, {"Fiscal Month", Int64.Type}, {"Fiscal Quarter", Int64.Type}, {"Desired Output: Qtr Offset", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each Number.ToText([Year]) &" "& [Month]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom.1", type date}}),
#"Added Custom6" = Table.AddColumn(#"Changed Type1", "Custom.5", each Date.QuarterOfYear(Date.AddMonths([Custom.1],2))-
Date.QuarterOfYear(Date.AddMonths(DateTime.LocalNow(),2))+
(Date.Year(Date.AddMonths([Custom.1],2))-Date.Year(Date.AddMonths(DateTime.LocalNow(),2)))*4)
in
#"Added Custom6"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
your formula looks good - maybe I'm missing something. Please check enclosed file with the Power Query solution for it. Next time, please include the columns that you've referenced in your formula in the sample data as well Thanks.
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 Guys
I have to ask does microsoft plan to update the DATESQTD(<dates>) measure to include an offset like DATESYTD(<dates> [,<year_end_date>]) for fiscal years?
Kind regards
Devon