cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Measure to filter IsCurrentFiscalPeriod 1,0 to filter report

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.

 

DimDate.JPG

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Post Patron
Post Patron

Re: Measure to filter IsCurrentFiscalPeriod 1,0 to filter report

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.

 

DAX Formula Error.JPG

 

Thanks

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Measure to filter IsCurrentFiscalPeriod 1,0 to filter report

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)

View solution in original post

8 REPLIES 8
Highlighted
Resolver III
Resolver III

Re: Measure to filter IsCurrentFiscalPeriod 1,0 to filter report

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.

Highlighted
Post Patron
Post Patron

Re: Measure to filter IsCurrentFiscalPeriod 1,0 to filter report

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.

 

DAX Formula Error.JPG

 

Thanks

View solution in original post

Highlighted
Resolver III
Resolver III

Re: Measure to filter IsCurrentFiscalPeriod 1,0 to filter report

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

Highlighted
Post Patron
Post Patron

Re: Measure to filter IsCurrentFiscalPeriod 1,0 to filter report

Ah Yes, this works great! Thanks @Michiel 


 

Highlighted
Post Patron
Post Patron

Re: Measure to filter IsCurrentFiscalPeriod 1,0 to filter report

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? 

Highlighted
Resolver III
Resolver III

Re: Measure to filter IsCurrentFiscalPeriod 1,0 to filter report

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.

Highlighted
Post Patron
Post Patron

Re: Measure to filter IsCurrentFiscalPeriod 1,0 to filter report

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.

 

fisc period.JPG

Highlighted
Post Patron
Post Patron

Re: Measure to filter IsCurrentFiscalPeriod 1,0 to filter report

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)

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors