Custom Fiscal Period True/False ignoring current time

[jpt1228]

Hello - I have 13 - 4 caIendar week periods in a year (Excluding leap year). Most years there are a couple days from the previous calendar year included in the next fiscal year.

I have a custom fiscal period column created in my DimDate table as well as a column to filter on IsCurrentFiscalPeriod = True/False as below:

IsCurrentFiscalPeriod =

VAR TodaysPeriod = MINX(FILTER(DimDate, DimDate[Date] =

TODAY()), DimDate[Fiscal Period])

RETURN

IF(dimdate[Fiscal Period] = TodaysPeriod, TRUE(), FALSE())

The problem I am having is when I filter the visual on current year it is excluding the 3 days of the fiscal period that fell within the previous calendar year. I don't know if there is a good formula or if I should just build an excel calendar table manually and use that.

Current example:

[amitchandak]

Not Sure I got it completely.

But see if this can help

https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[jpt1228]

Hello @amitchandak , I have custom fiscal periods consisting of every 28 days. I need to filter visuals on the current fiscal period based on today's date.

I think there are 2 issues: Custom Fical periods which I think I have found a solution to, but then filtering on the fiscal period when it falls between 2 calendar years.

[ChrisMendoza]

@jpt1228 -

I see you found my response @ https://community.powerbi.com/t5/Desktop/Calendar-table-with-13-periods/m-p/455906#M211260. I'm trying to find a mathematical solution so you do not have to enter [PeriodStart] and [PeriodEnd] for each of period of 28 days; so far no luck.

Possibly like my situation, you'll need to define each 28 day period to get 13 [PeriodIndex].

I reworked the code to attempt to accomadate 13 periods, the day counts are off because I do not know what your first start date was; I started with 01/01/2019 therefore it doesn't look like your sample.

let
    Calendar = #table(
	{"PeriodStart", "PeriodEnd" },
	{
		{ #date ( 2019, 01, 01 ), #date ( 2019, 01, 28 ) },
		{ #date ( 2019, 01, 29 ), #date ( 2019, 03, 02 ) },
		{ #date ( 2019, 03, 03 ), #date ( 2019, 03, 25 ) },
		{ #date ( 2019, 03, 26 ), #date ( 2019, 04, 27 ) },
		{ #date ( 2019, 04, 28 ), #date ( 2019, 05, 25 ) },
		{ #date ( 2019, 05, 26 ), #date ( 2019, 06, 22 ) },
		{ #date ( 2019, 06, 23 ), #date ( 2019, 07, 20 ) },
		{ #date ( 2019, 07, 21 ), #date ( 2019, 08, 17 ) },
		{ #date ( 2019, 08, 18 ), #date ( 2019, 09, 14 ) },
		{ #date ( 2019, 09, 15 ), #date ( 2019, 10, 12 ) },
		{ #date ( 2019, 10, 13 ), #date ( 2019, 11, 09 ) },
		{ #date ( 2019, 11, 10 ), #date ( 2019, 12, 07 ) },
		{ #date ( 2019, 12, 08 ), #date ( 2020, 01, 04 ) },
		{ #date ( 2020, 01, 05 ), #date ( 2020, 02, 01 ) },
		{ #date ( 2020, 02, 02 ), #date ( 2020, 02, 29 ) },
		{ #date ( 2020, 03, 01 ), #date ( 2020, 03, 28 ) },
		{ #date ( 2020, 03, 29 ), #date ( 2020, 04, 25 ) },
		{ #date ( 2020, 04, 26 ), #date ( 2020, 05, 23 ) },
		{ #date ( 2020, 05, 24 ), #date ( 2020, 06, 20 ) },
		{ #date ( 2020, 06, 21 ), #date ( 2020, 07, 18 ) },
		{ #date ( 2020, 07, 19 ), #date ( 2020, 08, 15 ) },
		{ #date ( 2020, 08, 16 ), #date ( 2020, 09, 12 ) },
		{ #date ( 2020, 09, 13 ), #date ( 2020, 10, 10 ) },
		{ #date ( 2020, 10, 11 ), #date ( 2020, 11, 07 ) },
		{ #date ( 2020, 11, 08 ), #date ( 2020, 12, 05 ) },
		{ #date ( 2020, 12, 06 ), #date ( 2021, 01, 02 ) }
	}
),
    #"Added PeriodIndex" = Table.AddIndexColumn(Calendar, "PeriodIndex", 1, 1),
    #"Added DatesBetween" = Table.AddColumn ( #"Added PeriodIndex", "Date", each List.Transform ( { Number.From ( [PeriodStart] ) ..Number.From ( [PeriodEnd] ) }, each Date.From ( _ ) ) ),
    #"Expanded DatesBetween" = Table.ExpandListColumn ( #"Added DatesBetween", "Date" ),
    #"Removed PeriodStartPeriodEnd" = Table.RemoveColumns ( #"Expanded DatesBetween", { "PeriodStart", "PeriodEnd" } ),
    #"Changed Type To Date" = Table.TransformColumnTypes(#"Removed PeriodStartPeriodEnd",{{"Date", type date}}),
    #"Added Fiscal Period" = Table.AddColumn(#"Changed Type To Date", "Fiscal Period", each if Number.Mod ( [PeriodIndex], 13 ) = 0 then 13 else Number.Mod([PeriodIndex], 13 ), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Added Fiscal Period", "Week of Year", each if Date.WeekOfYear([Date]) = 53 then 1 else Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Week of Year", "Year", each Date.Year([Date]), Int64.Type)
in
    #"Inserted Year"

[jpt1228]

@ChrisMendoza Yes I did, I was trying to figure out how to re-do the M code for 12 periods into 13 but it looks like you did it in your example so thanks for that. Can you explain the logic of dividing the period index? I see there is division of PeriodIndex / 13. What is the rest of the logic? = 0 then 13 else Number.Mod...

    #"Added Fiscal Period" = Table.AddColumn(#"Changed Type To Date", "Fiscal Period", each if Number.Mod ( [PeriodIndex], 13 ) = 0 then 13 else Number.Mod([PeriodIndex], 13 ), Int64.Type),

This custom fiscal period issue in by DimDate has been the most frustrating. Would be nice if someone smarter than me could create an option to generate a non-standard date table. Enter the dates as a parameter for start and stop or # of days in each period and then generate a date table. Most examples are for standard calendar months, modifying the start month but keeping periodicy to months which are no help.

[ChrisMendoza]

@jpt1228 -

Number.Mod finds the remainder so when [PeriodIndex] = a multiple of 13 the remainder is 0. If the remainder is 0 then that is what we'll call the 13th period. Honestly, a co-worker told me to use it when I was struggling with my custom calendar.

If you only used 

Number.Mod ( [PeriodIndex], 13 )

instead of the the if then else you can see where it places the 0's.

Maybe someone else can explain how that works mathematically.

I feel your pain with creating a custom calendar. The calendar I use has custom periods like 01/31/2020 - 02/29/2020 for the 9th Fiscal Period = '09-February'.

[jpt1228 · Accepted solution]

Hi @ChrisMendoza  Thanks to your M code to create a non-standard date table I was having an issue where the fiscal dates that fell in the previous calendar year would not show up in the correct fiscal year. If I modified your code to include one more column called FiscalYear and then added , "YEAR" }, to the end of each custom date it would bring in the correct Fiscal year. I have been looking all over for how to do this and couldn't find any M or DAX. Hopefully this will help the next person that has to deal with custom non, calendar fiscal time periods. This is also a great option to account for leap years.

let
Calendar = #table(
{"PeriodStart", "PeriodEnd", "FiscalYear" },
{ { #date ( 2017, 12, 31 ), #date ( 2018, 1, 27 ), "2018" },
{ #date ( 2018, 1, 28 ), #date ( 2018, 2, 24 ), "2018" },
{ #date ( 2018, 2, 25 ), #date ( 2018, 3, 24 ), "2018" },
{ #date ( 2018, 3, 25 ), #date ( 2018, 4, 21 ), "2018" },
{ #date ( 2018, 4, 22 ), #date ( 2018, 5, 19 ), "2018" },
{ #date ( 2018, 5, 20 ), #date ( 2018, 6, 16 ), "2018" },
{ #date ( 2018, 6, 17 ), #date ( 2018, 7, 14 ), "2018" },
{ #date ( 2018, 7, 15 ), #date ( 2018, 8, 11 ), "2018" },
{ #date ( 2018, 8, 12 ), #date ( 2018, 9, 8 ), "2018" },
{ #date ( 2018, 9, 9 ), #date ( 2018, 10, 6 ), "2018" },
{ #date ( 2018, 10, 7 ), #date ( 2018, 11, 3 ), "2018" },
{ #date ( 2018, 11, 4 ), #date ( 2018, 12, 1 ), "2018" },
{ #date ( 2018, 12, 2 ), #date ( 2018, 12, 29 ), "2018" },
{ #date ( 2018, 12, 30 ), #date ( 2019, 1, 26 ), "2019" },
{ #date ( 2019, 1, 27 ), #date ( 2019, 2, 23 ), "2019" },
{ #date ( 2019, 2, 24 ), #date ( 2019, 3, 23 ), "2019"},
{ #date ( 2019, 3, 24 ), #date ( 2019, 4, 20 ), "2019" },
{ #date ( 2019, 4, 21 ), #date ( 2019, 5, 18 ), "2019" },
{ #date ( 2019, 5, 19 ), #date ( 2019, 6, 15 ), "2019" },
{ #date ( 2019, 6, 16 ), #date ( 2019, 7, 13), "2019" },
{ #date ( 2019, 7, 14 ), #date ( 2019, 8, 10 ), "2019" },
{ #date ( 2019, 8, 11 ), #date ( 2019, 9, 7 ), "2019" },
{ #date ( 2019, 9, 8 ), #date ( 2019, 10, 5 ), "2019" },
{ #date ( 2019, 10, 6 ), #date ( 2019, 11, 2 ), "2019" },
{ #date ( 2019, 11, 3 ), #date ( 2019, 11, 30 ), "2019" },
{ #date ( 2019, 12, 1 ), #date ( 2019, 12, 28 ), "2019" },
{ #date ( 2019, 12, 29 ), #date ( 2020, 1, 25 ), "2020" },
{ #date ( 2020, 1, 26 ), #date ( 2020, 2, 22 ), "2020"},
{ #date ( 2020, 2, 23 ), #date ( 2020, 3, 21 ), "2020" },
{ #date ( 2020, 3, 22 ), #date ( 2020, 4, 18 ), "2020" },
{ #date ( 2020, 4, 19 ), #date ( 2020, 5, 16 ), "2020" },
{ #date ( 2020, 5, 17 ), #date ( 2020, 6, 13 ), "2020" },
{ #date ( 2020, 6, 14 ), #date ( 2020, 7, 11 ), "2020" },
{ #date ( 2020, 7, 12 ), #date ( 2020, 8, 8 ), "2020" },
{ #date ( 2020, 8, 9 ), #date ( 2020, 9, 5 ), "2020" },
{ #date ( 2020, 9, 6 ), #date ( 2020, 10, 3 ), "2020" },
{ #date ( 2020, 10, 4 ), #date ( 2020, 10, 31 ), "2020" },
{ #date ( 2020, 11, 1 ), #date ( 2020, 11, 28 ), "2020" },
{ #date ( 2020, 11, 29 ), #date ( 2021, 1, 2 ), "2020" }
}
),
#"Added PeriodIndex" = Table.AddIndexColumn(Calendar, "PeriodIndex", 1, 1),
#"Added DatesBetween" = Table.AddColumn ( #"Added PeriodIndex", "Date", each List.Transform ( { Number.From ( [PeriodStart] ) ..Number.From ( [PeriodEnd] ) }, each Date.From ( _ ) ) ),
#"Expanded DatesBetween" = Table.ExpandListColumn ( #"Added DatesBetween", "Date" ),
#"Changed Type To Date" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"Date", type date}}),
#"Added Fiscal Period" = Table.AddColumn(#"Changed Type To Date", "Fiscal Period", each if Number.Mod ( [PeriodIndex], 13 ) = 0 then 13 else Number.Mod([PeriodIndex], 13 ), Int64.Type),
InsertYear = Table.AddColumn(#"Added Fiscal Period", "Year", each Date.Year([Date]), type number),
InsertQuarter = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date]), type number),
InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]),type text),
InsertCalendarQtrOrder = Table.AddColumn(InsertCalendarQtr, "Quarter Year Order", each [Year] * 10 + [Quarter Num], type number),
InsertMonth = Table.AddColumn(InsertCalendarQtrOrder, "Month Num", each Date.Month([Date]), type number),
InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Date.ToText([Date], "MMMM"), type text),
InsertMonthNameShort = Table.AddColumn(InsertMonthName, "Month Name Short", each Date.ToText([Date], "MMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthNameShort, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text),
InsertCalendarMonthOrder = Table.AddColumn(InsertCalendarMonth, "Month Year Order", each [Year] * 100 + [Month Num], type number),
InsertWeek = Table.AddColumn(InsertCalendarMonthOrder, "Week Num", each Date.WeekOfYear([Date]), type number),
InsertCalendarWk = Table.AddColumn(InsertWeek, "Week Year", each "W" & Number.ToText([Week Num]) & " " & Number.ToText([Year]), type text),
InsertCalendarWkOrder = Table.AddColumn(InsertCalendarWk, "Week Year Order", each [Year] * 100 + [Week Num], type number),
InsertWeekEnding = Table.AddColumn(InsertCalendarWkOrder, "Week Ending", each Date.EndOfWeek([Date]), type date),
InsertDay = Table.AddColumn(InsertWeekEnding, "Month Day Num", each Date.Day([Date]), type number),
InsertDayInt = Table.AddColumn(InsertDay, "Date Int", each [Year] * 10000 + [Month Num] * 100 + [Month Day Num], type number),
InsertDayWeek = Table.AddColumn(InsertDayInt, "Day Num Week", each Date.DayOfWeek([Date]) + 1, type number),
InsertDayName = Table.AddColumn(InsertDayWeek, "Day Name", each Date.ToText([Date], "dddd"), type text),
InsertWeekend = Table.AddColumn(InsertDayName, "Weekend", each if [Day Num Week] = 1 then "Y" else if [Day Num Week] = 7 then "Y" else "N", type text),
InsertDayNameShort = Table.AddColumn(InsertWeekend, "Day Name Short", each Date.ToText([Date], "ddd"), type text),
InsertIndex = Table.AddIndexColumn(InsertDayNameShort, "Index", 1, 1),
InsertDayOfYear = Table.AddColumn(InsertIndex, "Day of Year", each Date.DayOfYear([Date]), type number),
InsertCurrentDay = Table.AddColumn(InsertDayOfYear, "Current Day?", each Date.IsInCurrentDay([Date]), type logical),
InsertCurrentWeek = Table.AddColumn(InsertCurrentDay, "Current Week?", each Date.IsInCurrentWeek([Date]), type logical),
InsertCurrentMonth = Table.AddColumn(InsertCurrentWeek, "Current Month?", each Date.IsInCurrentMonth([Date]), type logical),
InsertCurrentQuarter = Table.AddColumn(InsertCurrentMonth, "Current Quarter?", each Date.IsInCurrentQuarter([Date]), type logical),
InsertCurrentYear = Table.AddColumn(InsertCurrentQuarter, "Current Year?", each Date.IsInCurrentYear([Date]), type logical),
InsertCompletedDay = Table.AddColumn(InsertCurrentYear, "Completed Days", each if DateTime.Date(DateTime.LocalNow()) > [Date] then "Y" else "N", type text),
InsertCompletedWeek = Table.AddColumn(InsertCompletedDay, "Completed Weeks", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.WeekOfYear(DateTime.Date(DateTime.LocalNow())) > Date.WeekOfYear([Date])) then "Y" else "N", type text),
InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "Completed Months", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.Month(DateTime.Date(DateTime.LocalNow())) > Date.Month([Date])) then "Y" else "N", type text),
InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "Completed Quarters", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.QuarterOfYear(DateTime.Date(DateTime.LocalNow())) > Date.QuarterOfYear([Date])) then "Y" else "N", type text),
InsertCompletedYear = Table.AddColumn(InsertCompletedQuarter, "Completed Years", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else "N", type text),
#"Renamed Columns" = Table.RenameColumns(InsertCompletedYear,{{"PeriodStart", "FiscPeriodStart"}, {"PeriodEnd", "FiscPeriodEnd"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FiscPeriodEnd", type date}, {"FiscPeriodStart", type date}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "FiscalYearPeriod", each Text.Combine({[FiscalYear], Text.From([PeriodIndex], "en-US")}, "-"), type text)
in
#"Inserted Merged Column"

[ChrisMendoza]

@jpt1228 -

Yeah, I don't know how you'd mathematically find the [FYStart] with your need as those dates are dynamic. Seems like your solution/decision to build into the Source Table is best. Glad it helped.

Calendar = #table(
  {"PeriodStart", "PeriodEnd", "FiscalYear" },
  .
  .
  .
  { #date ( 2018, 12, 30 ), #date ( 2019, 1, 26 ), "2019" },
  .
  .
  .
  { #date ( 2019, 12, 29 ), #date ( 2020, 1, 25 ), "2020" },
  .
  .
  .
}),
  .
  .
  .

[amitchandak]

The example I shared in the blog. I created the following measure. Here My year is custom/ Non std year. And it displayed me the custom year data. Do not have this year's data. Tried last  year

This period Year = 
var _year = maxx(filter('Date','Date'[Date]=(TODAY()-365)),'Date'[Year])
return
CALCULATE(sum(Sales[Sales Amount]),all('Date'),'Date'[Year]=_year)

[jpt1228]

@amitchandak  I downloaded the .PBIX file for the non-sty-cal but the drop box link is broken. I presume this was an Excel calendar that was created with custom time periods and then imported into the data model?

Thanks

[amitchandak]

https://www.dropbox.com/s/hwfta7rhog0iwn4/sales_non_std_cal.pbix?dl=0