Filter for visual to display data only for 12 last complete financial periods ( Today is 17/05/23 and this is the middle of Period 11 so this period is still open and not complete)
This slicer should show only financial complete periods and the last complete financial period is Period 10, What should I do to remove other incomplete periods (Periods 11 and 12)
Would you please guide me on how I should achieve my targets?
The simplest way to do this is to have a column in the date table marking dates prior to current period.
Assuming your data is real time ish then we can work with today and add the following column to the Calendar
IsBeforeCurrentPeriod =
Var BeginningOfPeriod = Date(Year(today()),Month(today()),1)
Return
IF('Calendar'[Date]<BeginningOfPeriod,1,0)
Then bring this column into the visual filter and filter for 1.
An alternative solution which will offer date flexibility across the whole solution and make time intelligence easier is to have a period offset column reading 0 for current month, -1 for last month, -2 for 2 months ago, 1 for next month etc.
@Melissa 's date table in power query offers this but I’m not sure whether it works the year offset through for non-calendar year.
Leavers_forum .pbix (411.5 KB)
I was playing around, and did what Pete suggested, without knowing he suggested it I basically produced a calculated column for the last 12 periods fnot including the current period, and added it as a filter to the slicer visual to restrict it, exactly what Peter said.
You would have to play around with the date math to get exactly what you wanted.
Leavers_forum .pbix (411.7 KB)
You just need to adjust the calculated column for the requirement, I have changed the column to all periods before the current period, based off todays date, the column should show 1 for completed periods, it is this column that is used in the filter in the slice to only show values with 1, the same would apply to any visual you wanted to filter to only previous periods.
I basically produced a calculated column for the last 12 periods fnot including the current period, and added it as a filter to the slicer visual to restrict it, exactly what Peter said.