Issue with Date slicer that contains "Last Completed Week" and "Current Week" as values

[gvenkatesh_alti]

Hi Community, 

Need help with the below issue i am facing when a new quarter/month/Week comes .

I have a date table ( data sample shown in images ) . There is a requirement to show hierarchy within slicer to start from Quarter > Period(month) > Week . Users will select weeks based on their interest . 

In my org , the first quarter starts from February . 

Q1 -  February to April  ( Period 01 , Period 02 , Period 03 ) 

Q2 - May to July ( Period 04 , Period 05 , Period 06 ) 

Q3 - August to October ( Period 07 , Period 08 , Period 09 ) 

Q4 - November to January ( Period 10 , Period 11 , Period 12 ) 

We do not have values like "Latest Quarter" ," Latest period" , "Last Completed Week" , "Current Week" coming from data source (view) . So , I designed some calculated columns to handle those logics . 

Latest quarter - Is the current quarter which is Q3 [ Q3 - August to October ( Period 07 , Period 08 , Period 09 ) ]

Latest Period -  is the latest month within Q3 ( august - period 08 ) 

Last Completed week  - August 4th to 10th within Current quarter and period 

Current week -   August 11th to 17th within Current quarter and period  .

Now the issue is - when a new quarter and period starts , the "Last Completed week" and "current week"  will belongs to different quarter and period . the filter is by default selected to "last completed week" in report . So, "Last completed week" started appearing at 2 places . one in the "latest period " and "Latest quarter " and other in respective qtr and period it actually belongs to . Because of this, data in report looks blank as filter doesnt able to reset "last completed week" to its actual qtr and period.  

Calculated columns i created are - 

Is Latest Quarter =

VAR Quarter = 'Dates'[FISCAL_QUARTER_OFFSET_NUM]

VAR CurrentQuarterOffset = CALCULATE(MAX(Dates[FISCAL_QUARTER_OFFSET_NUM]),FILTER(ALL(Dates),Dates[DATE_DATE] = TODAY()))

VAR MaxQuarterOffset = IF(ISBLANK(CurrentQuarterOffset), CALCULATE(MAX(Dates[FISCAL_QUARTER_OFFSET_NUM]),FILTER(ALL(Dates),Dates[DATE_OFFSET_NUM] = 0)), CurrentQuarterOffset)

VAR FutureQtrOffset = MaxQuarterOffset + 1

VAR MinYearOffset = CALCULATE(MAX(Dates[FISCAL_YEAR_OFFSET_NUM]),FILTER(ALL(Dates),Dates[DATE_DATE] = TODAY()))

VAR MinYearOffsetFallback = IF(ISBLANK(MinYearOffset), CALCULATE(MAX(Dates[FISCAL_YEAR_OFFSET_NUM]),FILTER(ALL(Dates),Dates[DATE_OFFSET_NUM] = 0)) - 3, MinYearOffset - 3)

RETURN

SWITCH(TRUE(),

    Quarter = FutureQtrOffset, "Next Quarter",

    Quarter = MaxQuarterOffset, "Latest Quarter",

    Quarter < MaxQuarterOffset && Dates[FISCAL_YEAR_OFFSET_NUM] > MinYearOffsetFallback, 'Dates'[FISCAL_QUARTER_SHORT_NAME],

    BLANK()

)

Is Latest Period =

VAR Period = 'Dates'[FISCAL_PERIOD_OFFSET_NUM]

VAR CurrentPeriodOffset = CALCULATE(MAX(Dates[FISCAL_PERIOD_OFFSET_NUM]),FILTER(ALL(Dates),Dates[DATE_DATE] = TODAY()))

VAR MaxPeriodOffset = IF(ISBLANK(CurrentPeriodOffset), CALCULATE(MAX(Dates[FISCAL_PERIOD_OFFSET_NUM]),FILTER(ALL(Dates),Dates[DATE_OFFSET_NUM] = 0)), CurrentPeriodOffset)

VAR FuturePeriodOffset = MaxPeriodOffset + 1

VAR MinYearOffset = CALCULATE(MAX(Dates[FISCAL_YEAR_OFFSET_NUM]),FILTER(ALL(Dates),Dates[DATE_DATE] = TODAY()))

VAR MinYearOffsetFallback = IF(ISBLANK(MinYearOffset), CALCULATE(MAX(Dates[FISCAL_YEAR_OFFSET_NUM]),FILTER(ALL(Dates),Dates[DATE_OFFSET_NUM] = 0)) - 3, MinYearOffset - 3)

RETURN

SWITCH(TRUE(),

    Period = FuturePeriodOffset, "Next Period",

    Period = MaxPeriodOffset, "Latest Period",

    Period < MaxPeriodOffset && Dates[FISCAL_YEAR_OFFSET_NUM] > MinYearOffsetFallback, 'Dates'[FISCAL_PERIOD_SHORT_NAME],

    BLANK()

)

Is Last Complete Week =

VAR Week = 'Dates'[FISCAL_WEEK_OFFSET_NUM]

VAR CurrentWeekOffset = CALCULATE(MAX(Dates[FISCAL_WEEK_OFFSET_NUM]),FILTER(ALL(Dates),Dates[DATE_DATE] = TODAY()))

VAR MaxWeekOffset = IF(ISBLANK(CurrentWeekOffset), CALCULATE(MAX(Dates[FISCAL_WEEK_OFFSET_NUM]),FILTER(ALL(Dates),Dates[DATE_OFFSET_NUM] = 0)), CurrentWeekOffset)

VAR LastCompleteWeekOffset = MaxWeekOffset - 1

VAR MinYearOffset = CALCULATE(MAX(Dates[FISCAL_YEAR_OFFSET_NUM]),FILTER(ALL(Dates),Dates[DATE_DATE] = TODAY()))

VAR MinYearOffsetFallback = IF(ISBLANK(MinYearOffset), CALCULATE(MAX(Dates[FISCAL_YEAR_OFFSET_NUM]),FILTER(ALL(Dates),Dates[DATE_OFFSET_NUM] = 0)) - 3, MinYearOffset - 3)

RETURN

SWITCH(TRUE(),

    Week = MaxWeekOffset, "Current Week",

    Week = LastCompleteWeekOffset, "Last Completed Week",

    Week < LastCompleteWeekOffset && Dates[FISCAL_YEAR_OFFSET_NUM] > MinYearOffsetFallback, 'Dates'[FISCAL_WEEK_NAME],

    BLANK()

)

Below images shows how "last completed week" is appearing at 2 different places everytime a new quarter or period starts . 

please assist. 

[v-veshwara-msft]

Hi @gvenkatesh_alti ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.

[v-veshwara-msft]

Hi @gvenkatesh_alti ,

Following up to see if your query is resolved and if any of the responses helped.
If you still need assistance, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @gvenkatesh_alti ,

Just checking in to see if you query is resolved and if response from @jaineshp  was helpful.
Otherwise, feel free to reach out for further assistance.

Thank you.

Thanks @jaineshp for your valuable guidance.

[jaineshp]

Hey @v-veshwara-msft,

Thank you for the kind recognition - always happy to contribute to our community's success!

Best Regards,
Jainesh Poojara | Power BI Developer

[jaineshp · Accepted solution]

Hey @gvenkatesh_alti,

Looking at your issue, the problem is that your calculated columns are creating duplicate entries for "Last Completed Week" when it spans across different quarters/periods. Here's how to fix it:

Solution Steps:

1. Modify your hierarchy approach - Instead of using calculated columns that create duplicates, use a single calculated column that shows the actual quarter/period the week belongs to, not where it appears contextually.
2. Create a separate "Current Context" table - Build a small reference table with just:
   • Current Week
   • Last Completed Week
   • Latest Period
   • Latest Quarter
3. Use measures instead of calculated columns for dynamic labels:

Current Week Measure =
IF(MAX(Dates[FISCAL_WEEK_OFFSET_NUM]) =
CALCULATE(MAX(Dates[FISCAL_WEEK_OFFSET_NUM]),
FILTER(ALL(Dates), Dates[DATE_DATE] = TODAY())),
"Current Week",
BLANK())

1. Implement conditional formatting in your slicer - Use the measure to highlight current/last completed weeks visually instead of renaming them.
2. Set default filter via bookmark - Create a bookmark that automatically selects the correct week in its actual quarter/period location, then set this as your report's default state.

Quick Fix: Remove the text labels from calculated columns and use visual indicators (colors/icons) to show current vs completed weeks. This eliminates the duplicate issue entirely while maintaining user clarity.

The key is separating the display logic from the actual date hierarchy structure.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer