Forum Discussion
jpt1228
Responsive Resident
Responsive ResidentCustom Fiscal Period True/False ignoring current time
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:
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"
10 Replies
- amitchandak
Super User
Not Sure I got it completely.
But see if this can help
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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
jpt1228Hello 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.
- 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)
