Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

Calendar Fiscal Quarter Offset M Code for Function

‎03-02-2020 12:21 PM

Hi Community!

 

I have a calendar function and I need to work out how to get the 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. The other offsets like Calendar Quarter, Calendar/Fiscal Month and Calendar/Fiscal Year work fine.


Has anyone created a function with a Fiscal Quarter offset?

 

Here's my code:

let
    StartDate = #date(2016, 11, 1),
    EndDate = #date(2020, 10, 31) 
    CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
    FiscalYearEndMonth = 10,
    #"==SET PARAMETERS ABOVE==" = 1,
    #"==Build Date Column==" = #"==SET PARAMETERS ABOVE==",
    ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),
    #"==Add Calendar Columns==" = #"Changed Type to Date",
    #"Added Calendar MonthNum" = Table.AddColumn(#"==Add Calendar Columns==", "MonthNum", each Date.Month([Date]), Int64.Type),
    #"Added Month Name" = Table.AddColumn(#"Added Calendar MonthNum", "Month", each Text.Start(Date.MonthName([Date]),3), type text),
    #"Added Month Name Long" = Table.AddColumn(#"Added Month Name", "MonthLong", each Date.MonthName([Date]), type text),
    #"Added Calendar Quarter" = Table.AddColumn(#"Added Month Name Long", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date]))),
    #"Added QuarterNum" = Table.AddColumn(#"Added Calendar Quarter", "QuarterNum", each Text.From(Date.QuarterOfYear([Date]))),
    #"Added Calendar Year" = Table.AddColumn(#"Added QuarterNum", "Year", each Date.Year([Date]), Int64.Type),
    #"==Add Fiscal Calendar Columns==" = #"Added Calendar Year",
    #"Added FiscalMonthNum" = Table.AddColumn(#"==Add Fiscal Calendar Columns==", "FiscalMonthNum", each if [MonthNum] > FiscalYearEndMonth
then [MonthNum] - FiscalYearEndMonth
else [MonthNum] + (12 - FiscalYearEndMonth), type number),
    #"Added FiscalMonth Name" = Table.AddColumn(#"Added FiscalMonthNum", "FiscalMonth", each [Month]),
    #"Added FiscalMonth Name Long" = Table.AddColumn(#"Added FiscalMonth Name", "FiscalMonthLong", each [MonthLong]),
    #"Added FiscalQuarter" = Table.AddColumn(#"Added FiscalMonth Name Long", "FiscalQuarter", each "FQ" & Text.From(Number.RoundUp([FiscalMonthNum] / 3,0))),
    #"Added FiscalQuarterNum" = Table.AddColumn(#"Added FiscalQuarter", "FiscalQuarterNum", each Number.RoundUp([FiscalMonthNum] / 3,0)),
    #"Added PrevFiscalQuarterNum" = Table.AddColumn(#"Added FiscalQuarterNum", "Previous FiscalQuarterNum", each if [FiscalQuarterNum] = 1 then 4 else Number.RoundUp([FiscalMonthNum] / 3 -1, 0)),
    #"Added FiscalYear" = Table.AddColumn(#"Added PrevFiscalQuarterNum", "FiscalYear", each "FY" & Text.End(Text.From(if [MonthNum] > FiscalYearEndMonth then [Year] + 1 else [Year]), 2)),
    #"==Add Calendar Date Offset Columns==" = #"Added FiscalYear",
    #"Added CurMonthOffset" = Table.AddColumn(#"==Add Calendar Date Offset Columns==", "CurMonthOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate) ) * 12 + Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
    #"Added CurQuarterOffset" = Table.AddColumn(#"Added CurMonthOffset", "CurQuarterOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate)) * 4 + Number.RoundUp(Date.Month([Date]) / 3) - Number.RoundUp(Date.Month(CurrentDate) / 3)),
    #"Added CurYearOffset" = Table.AddColumn(#"Added CurQuarterOffset", "CurYearOffset", each Date.Year([Date]) - Date.Year(CurrentDate), Int64.Type),
    #"Added FutureDate Flag" = Table.AddColumn(#"Added CurYearOffset", "FutureDate", each if [Date] > CurrentDate then "Future" else "Past" ),
    #"==Add FiscalYearOffset==" = #"Added FutureDate Flag",
    #"Filtered Rows to CurrentDate" = Table.SelectRows(#"==Add FiscalYearOffset==", each ([Date] = CurrentDate)),
    CurrentFiscalYear = #"Filtered Rows to CurrentDate"{0}[FiscalYear],
    #"Continue...Orig Table" = #"==Add FiscalYearOffset==",
    #"Added CurFiscalYearOffset" = Table.AddColumn(#"Continue...Orig Table", "CurFiscalYearOffset", each Number.From(Text.Range([FiscalYear],2,2)) - Number.From(Text.Range(CurrentFiscalYear,2,2)))
in
    #"Added CurFiscalYearOffset"

 

Labels:
Message 1 of 7
1,657 Views
0
6 REPLIES 6
Anonymous
Not applicable

‎03-10-2020 08:47 AM

Hi Community - I wonder if anyone has any suggestions on this issue I'm trying to resolve!

 

Many thanks!

Message 7 of 7
1,569 Views
0
v-chuncz-msft
Community Support
Community Support

‎03-04-2020 02:04 AM

@Anonymous 

 

You may use Date.AddMonths to add 2 months.

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 5 of 7
1,600 Views
0
Anonymous
Not applicable
In response to v-chuncz-msft

‎03-04-2020 05:05 AM

Hi @v-chuncz-msft,

 

Could you elaborate how Date.AddMonths will work in my situation?

 

Our fiscal year ends in October, so from

Nov-Jan = Q1

Feb-Apr = Q2

May-Jul = Q3

Aug-Oct = Q4

 

For instance, we are currently in March 2020, so

Feb 2020's FiscalQtr Offset = 0; 

Jan 2020's FiscalQtr Offset = -1;

Dec 2019's FiscalQtr Offset = -1;

Nov 2019's FiscalQtr Offset = -1;

Oct 2019's FiscalQtr Offset = -2;

Sep 2019's FiscalQtr Offset = -2;

...

...

Jun 2019's FiscalQtr Offset = -3....

Message 6 of 7
1,589 Views
0
Anonymous
Not applicable

‎03-02-2020 01:09 PM

@Anonymous  I used this 

FY Quaters =
SWITCH(true(),
'Dates qtr'[Month Number] = 1, "Q3",
'Dates qtr'[Month Number] = 2, "Q3",
'Dates qtr'[Month Number] = 3, "Q3",
'Dates qtr'[Month Number] = 4, "Q4",
'Dates qtr'[Month Number] = 5, "Q4",
'Dates qtr'[Month Number] = 6, "Q4",
'Dates qtr'[Month Number] = 7, "Q1",
'Dates qtr'[Month Number] = 8, "Q1",
'Dates qtr'[Month Number] = 9, "Q1",
'Dates qtr'[Month Number] = 10, "Q2",
'Dates qtr'[Month Number] = 11, "Q2",
'Dates qtr'[Month Number] = 12, "Q2",
BLANK()
)
Message 4 of 7
1,633 Views
0
ImkeF
Super User
Super User

‎03-02-2020 12:28 PM

Hi @Anonymous   for a better understanding: Could you please share the code you have so far

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

Message 2 of 7
1,654 Views
0
Anonymous
Not applicable
In response to ImkeF

‎03-02-2020 12:34 PM

Here's my code: 

let
    StartDate = #date(2016, 11, 1),
    EndDate = #date(2020, 10, 31) 
    CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
    FiscalYearEndMonth = 10,
    #"==SET PARAMETERS ABOVE==" = 1,
    #"==Build Date Column==" = #"==SET PARAMETERS ABOVE==",
    ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),
    #"==Add Calendar Columns==" = #"Changed Type to Date",
    #"Added Calendar MonthNum" = Table.AddColumn(#"==Add Calendar Columns==", "MonthNum", each Date.Month([Date]), Int64.Type),
    #"Added Month Name" = Table.AddColumn(#"Added Calendar MonthNum", "Month", each Text.Start(Date.MonthName([Date]),3), type text),
    #"Added Month Name Long" = Table.AddColumn(#"Added Month Name", "MonthLong", each Date.MonthName([Date]), type text),
    #"Added Calendar Quarter" = Table.AddColumn(#"Added Month Name Long", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date]))),
    #"Added QuarterNum" = Table.AddColumn(#"Added Calendar Quarter", "QuarterNum", each Text.From(Date.QuarterOfYear([Date]))),
    #"Added Calendar Year" = Table.AddColumn(#"Added QuarterNum", "Year", each Date.Year([Date]), Int64.Type),
    #"==Add Fiscal Calendar Columns==" = #"Added Calendar Year",
    #"Added FiscalMonthNum" = Table.AddColumn(#"==Add Fiscal Calendar Columns==", "FiscalMonthNum", each if [MonthNum] > FiscalYearEndMonth
then [MonthNum] - FiscalYearEndMonth
else [MonthNum] + (12 - FiscalYearEndMonth), type number),
    #"Added FiscalMonth Name" = Table.AddColumn(#"Added FiscalMonthNum", "FiscalMonth", each [Month]),
    #"Added FiscalMonth Name Long" = Table.AddColumn(#"Added FiscalMonth Name", "FiscalMonthLong", each [MonthLong]),
    #"Added FiscalQuarter" = Table.AddColumn(#"Added FiscalMonth Name Long", "FiscalQuarter", each "FQ" & Text.From(Number.RoundUp([FiscalMonthNum] / 3,0))),
    #"Added FiscalQuarterNum" = Table.AddColumn(#"Added FiscalQuarter", "FiscalQuarterNum", each Number.RoundUp([FiscalMonthNum] / 3,0)),
    #"Added PrevFiscalQuarterNum" = Table.AddColumn(#"Added FiscalQuarterNum", "Previous FiscalQuarterNum", each if [FiscalQuarterNum] = 1 then 4 else Number.RoundUp([FiscalMonthNum] / 3 -1, 0)),
    #"Added FiscalYear" = Table.AddColumn(#"Added PrevFiscalQuarterNum", "FiscalYear", each "FY" & Text.End(Text.From(if [MonthNum] > FiscalYearEndMonth then [Year] + 1 else [Year]), 2)),
    #"==Add Calendar Date Offset Columns==" = #"Added FiscalYear",
    #"Added CurMonthOffset" = Table.AddColumn(#"==Add Calendar Date Offset Columns==", "CurMonthOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate) ) * 12 + Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
    #"Added CurQuarterOffset" = Table.AddColumn(#"Added CurMonthOffset", "CurQuarterOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate)) * 4 + Number.RoundUp(Date.Month([Date]) / 3) - Number.RoundUp(Date.Month(CurrentDate) / 3)),
    #"Added CurYearOffset" = Table.AddColumn(#"Added CurQuarterOffset", "CurYearOffset", each Date.Year([Date]) - Date.Year(CurrentDate), Int64.Type),
    #"Added FutureDate Flag" = Table.AddColumn(#"Added CurYearOffset", "FutureDate", each if [Date] > CurrentDate then "Future" else "Past" ),
    #"==Add FiscalYearOffset==" = #"Added FutureDate Flag",
    #"Filtered Rows to CurrentDate" = Table.SelectRows(#"==Add FiscalYearOffset==", each ([Date] = CurrentDate)),
    CurrentFiscalYear = #"Filtered Rows to CurrentDate"{0}[FiscalYear],
    #"Continue...Orig Table" = #"==Add FiscalYearOffset==",
    #"Added CurFiscalYearOffset" = Table.AddColumn(#"Continue...Orig Table", "CurFiscalYearOffset", each Number.From(Text.Range([FiscalYear],2,2)) - Number.From(Text.Range(CurrentFiscalYear,2,2)))
in
    #"Added CurFiscalYearOffset"
Message 3 of 7
1,651 Views
0

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

View All