Measure to filter IsCurrentFiscalPeriod 1,0 to filter report

[jpt1228]

Hello, I posted this in another message but can't seem to get a response from the Microsoft team beyone the first reply.

I need to filter reports automatically based on the current date being the current fiscal period. There are 13 fiscal periods in the year consisting of 4 weeks. All of the formulas I have found refer to adjusted Year, Quarter, Months. I cannot find any references to fiscal period that is not Year, Quarter, Month.

I have a DimDate table with the date, fiscal period roundup(week in year / 4), and Today which is true or false depending on the current date.

I cannot figure out how to create a column that does the same as Today but for the Fiscal Period. I want to be able to drag this measure into my reports to automatically filter to the current period and then change to next period when the date falls into that fiscal period.

[jpt1228 · Accepted solution]

Hello @Michiel  I am able to write the formula up until the IF([Fiscal Period] Part. Fiscal Period is a custom column not a measure. It is a numeric field.

Thanks

[Michiel]

Hi,

Try something like

ThisFiscalPeriod =
VAR TodaysPeriod = MINX(FILTER(DimDate, DimDate[Date] = TODAY()), DimDate[Fiscal Period])
RETURN
IF( [Fiscal Period] = TodaysPeriod, TRUE(), FALSE() )

This assumes that [Fiscal Period] is a numeric field that wil work with MINX. If its a text field, just use CONCATENATEX instead of MINX.

[jpt1228]

Hi @Michiel The DAX you provided works to return True/False for current fiscal period. However, it returns the current fiscal period and then returns true for all years and causes filtering issues. Is there a way to filter the current fiscal year/period based on the current date only, and return false for any fiscal period that occurs outside of the current one? 

[Michiel]

Hi @jpt1228 , I do hope it has worked for you during the past 9 months 😉

Your 'PLO Fiscal Period' column probably contains values that do not depend on the year, e.g. 'P2' as opposed to '2019 P2'. If you use a year-dependent column, it should work. After all, you just determine the fiscal period of today and use that for comparison. 

If you don't have a column like that, do the same thing with two columns, fiscal period and year together. Create another variable TodaysFiscalYear, and use both variables in the IF statement.

[jpt1228]

Hi @Michiel Thanks for the recommendation it worked but.. Now the problem I am having is for example: Period 13 Fiscal Year 2019 Ended 12/28/2019 and the new fiscal year period 1 begins on 12/29/2019. The formula is returning false for Current Fiscal Period? for 12/29/2019, 12/30/2019, 12/31/2019 and True for all other dates in period 1 year 2020.

[jpt1228 · Accepted solution]

I was able to add to the M code for the calendar to define each custom fiscal period and also the custom fiscal year by adding the bold. Then add the columns for FiscalYearPeriod and the logic to check if CurrentFiscalPeriod as above.

let
Calendar = #table(
{"PeriodStart", "PeriodEnd", "FiscalYear" },
{
{ #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)

[jpt1228 · Accepted solution]

Hello @Michiel  I am able to write the formula up until the IF([Fiscal Period] Part. Fiscal Period is a custom column not a measure. It is a numeric field.

Thanks

[Michiel]

You're trying to do this as a calculated column, right? The error message typically appears in measures when refering to a column directly.

[jpt1228]

Ah Yes, this works great! Thanks @Michiel 

---