Dynamic Measure developement

[ACSpotlight]

Hi I am wanting to develop a quite complex solution.

The basis is I have a business running off a retail based calendar 4,5,4 week periods etc.  The requirement is to have a slicer that selects a retail period id from by date dimension table that is used as a anchor point.  Then another slicer that gives the user the option to view data by time intelligence with the below options

MMT (numbers for just the retail period selected in the slicer)
MQT (numbers for the last 3 retail periods)
MAT (numbers for the last 12 retail periods)
FINYTD (number from the start of the fiscal year the retail period is in)

The requirement is to display the various measures, sales, dictinct count of customers etc in line charts that the axis is has dynamic granularity determined by the  time intelligence selection where MMT is displayed by day, MQT is displayed by week (mon-sun), MAT is displayed by retail period and FINYTD is varaible in that if the retail period selected in the first slicer is the first retail periode  of the month it displays by day, if it is the 2nd or 3rd period of the month it displays by week and if it is the 4 or higher period it displays by period.

The measures will also be displayed in table charts to display the total and be compared against measures for the previous year.  

what is the best solution that will drive the fastest performance

[v-ssriganesh]

Hello @ACSpotlight,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hello @ACSpotlight,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hi @ACSpotlight,
Thank you for reaching out to the Microsoft fabric community forum.

The best solution for your scenario is to use Power BI’s calendar-based time intelligence features, which work natively with retail calendars like 4-5-4 and allow full flexibility on how you group and analyze time periods. This approach supports dynamic slicers, lets you switch between period, week or day views and provides faster performance compared to older methods.

Make sure your date dimension is set up for your retail calendar and marked as a date table. Leverage the new time intelligence functions these enable dynamic periods and granular axes without complex code, and calculation groups or field parameters make it easy to switch measure logic and visual granularity based on user selection.

This method will ensure smooth, flexible reporting and optimal performance for your complex time intelligence requirements.

 
Best regards,
Ganesh Singamshetty.

[johnt75]

Firstly, given that you are using a 4,5,4 calendar I would recommend that you use the new time intelligence functions which are calendar based, introduced in the September 2025 update. SQLBI have an excellent article on this new preview feature at https://www.sqlbi.com/articles/introducing-calendar-based-time-intelligence-in-dax/ .

For the time intelligence calculations, I would create a calculation group with items for the different periods you want to show, and present these options to the user in a slicer.

For the dynamic date range on the chart axis, create a field parameter which contains the different granularities of date you wish to show - day, week, period. Use this field parameter as the X-axis.

You will need to add a couple of new columns to the table created for the field parameter, to store the minimum and maximum number of days for which you want to use that granularity.

Go into the table view and select the field parameter table. You will see the DAX code which creates the table, each row has the name of the column, the table reference and an integer, which is the sort order. After the sort order you need to add 2 new numbers, separated by a comma. The first will be the minimum number of days to show at that granularity, the second is the maximum.

The minimum for the first entry should be 0. The maximum for the last entry should be BLANK(). For every other entry the minimum value should be the same as the maximum of the previous entry.

When you have added the new columns, change their names to "Min Days" and "Max Days".

Create a measure like

Choose Date Precision = 
VAR FirstVisibleDate = MIN( 'Date'[Date] )
VAR LastVisibleDate = MAX( 'Date'[Date] )
VAR DaysDiff = DATEDIFF( FirstVisibleDate, LastVisibleDate, DAY)
VAR MinDays = SELECTEDVALUE( 'Date Precision'[Min Days] )
VAR MaxDays = SELECTEDVALUE( 'Date Precision'[Max Days] )
VAR Result = IF( MinDays <= DaysDiff && ( ISBLANK( MaxDays ) || MaxDays > DaysDiff ), 1, 0 )

RETURN Result

Add this measure as a TOPN filter on the field parameter, set to show only the top 1 items.

Now when you change the number of dates visible through your time intellgence calculation items the granularity of the axis should change dynamically.