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: 

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.

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,352 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,264 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,295 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,284 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,328 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,349 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,346 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

View All