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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Calendar Fiscal Quarter Offset M Code for Function

Hi Community!

 

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"

 

6 REPLIES 6
Anonymous
Not applicable

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

 

Many thanks!

v-chuncz-msft
Community Support
Community Support

@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.
Anonymous
Not applicable

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....

Anonymous
Not applicable

@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()
)
ImkeF
Super User
Super User

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

Anonymous
Not applicable

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"

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.