Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calendar Fiscal Quarter Offset

Hi Everyone!

 

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:

YearMonthFiscal MonthFiscal QuarterDesired Output: Qtr Offset
2018Nov11-5
2018Dec21-5
2019Jan31-5
2019Feb42-4
2019Mar52-4
2019Apr62-4
2019May73-3
2019Jun83-3
2019Jul93-3
2019Aug104-2
2019Sep114-2
2019Oct124-2
2019Nov11-1
2019Dec21-1
2020Jan31-1
2020Feb420
2020Mar52Now ---> 0
2020Apr620
2020May731
2020Jun831
2020Jul931
2020Aug1042
2020Sep1142
2020Oct1242
1 ACCEPTED SOLUTION
dax
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
dax
Community Support
Community Support

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.

ImkeF
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors