cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jpt1228 Member
Member

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
jpt1228 Member
Member

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

4 REPLIES 4
Michiel Member
Member

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.

jpt1228 Member
Member

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

Michiel Member
Member

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.

jpt1228 Member
Member

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

Ah Yes, this works great! Thanks @Michiel