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

Automatically Filter Report to Current 4 Week Fiscal Period

Hello, I have been searching but have not found the solution yet. My company has 13 - 4 Week periods which make up the year. Each week is basically the ISO Week in year + 1 to start on Dec 30 2018.

 

I need to automatically filter visuals based on the current fiscal period. When the date changes to the next fiscal period then I want to filter all the visuals to that period.

 

I have a DimDate table with Date, ISO Weeks in year, Fiscal Period, Is Current Day (True,False).

 

I am looking to create a column or measure that I can filter the page on is current Period = True so it will dynamically filter the report.

DimDateFiscal.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
jpt1228 Member
Member

Re: Automatically Filter Report to Current 4 Week Fiscal Period

Here is the solution I was looking for in another thread. Note: The formula is adding a column not a measure.

 

https://community.powerbi.com/t5/Desktop/Measure-to-filter-IsCurrentFiscalPeriod-1-0-to-filter-repor...

 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Automatically Filter Report to Current 4 Week Fiscal Period

Hi @jpt1228 ,

It seems that you want to create a measure/calculated column to filter the current fiscal period.

You could create a measure/calculated column with the logic below.

column/measure=if([column]=[current fiscal period],1,0)

Then drag this column or measure in the page level filter and show with 1, it will filter the data for current fiscal period.

If you still need help, please share your data sample with table format and your desired output so that we could help further on it.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Mariusz Established Member
Established Member

Re: Automatically Filter Report to Current 4 Week Fiscal Period

Hi @jpt1228 

I always rank my date periods in the calendar table in the query editor, for example with weeks.

add custom column
SeqWeek  = [Week] - Date.WeekOfYear(DateTime.LocalNow())
where [Week] is your week number column, this will return 0 on a current week and -1 on the previous one and so on.
You will need to do the same with Year
add custom column:

SeqYear = [Year] - Date.Year(DateTime.LocalNow())
where [Year] is your Year column, this will return 0 on a current Year.
Now when you load the data you can filter your report to seqYear 0 and seqWeek to between -3 and 0.

Hope this helps 
Mariusz


jpt1228 Member
Member

Re: Automatically Filter Report to Current 4 Week Fiscal Period

Hi @v-piga-msft . This is the solution I was thinking of however, I don't have a column that identifies the current fiscal period. I have a column that identifies is current week True/False.

 

How would I add a custom column to identify is current fiscal period?

 

ThankDimDate.JPG

jpt1228 Member
Member

Re: Automatically Filter Report to Current 4 Week Fiscal Period

Here is the solution I was looking for in another thread. Note: The formula is adding a column not a measure.

 

https://community.powerbi.com/t5/Desktop/Measure-to-filter-IsCurrentFiscalPeriod-1-0-to-filter-repor...