Forum Discussion
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.
Here is the solution I was looking for in another thread. Note: The formula is adding a column not a measure.
4 Replies
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
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?
Thank
Here is the solution I was looking for in another thread. Note: The formula is adding a column not a measure.
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
