How to - Make a dynamic continous X axis based on filters in slicers

[Randcharles]

Dear all, 

I have a graphic visual that should be filtered from 2 slicers ( Periodicity, Period). I created these 2 slicers based on  Field parameter. 
For my current need, according to the filter selected in Periodicity and period ( Monthly, Weekly, Daily), my visual graphic should show data from a given date/ period untill the selected period in my filter. 
For example, if I select Monthly periodicity and select a month :  I need to show data in my visual graphic 3 months untill the selected month.

Result should be something like this : 

The same, example for Weekly periodicity, when user select a week for period :  visual should also show me a couple of weeks before the selected week: 

Result of this filter should be : 

For the moment, I am not be able to implement this with my filter and my graph. It takes only the period ( week, day, month) that I selected not the continous period until the selected period.

Any idea how to obtain the same result as I show previously on my graph ?

Here is my model :

Thanks in advance.

[Randcharles]

Hi @v-jtian-msft ?
Thank you very much for help. The code that you provide is very helpfull for me.
So if I underestand, I can't use the parameter to implement "Periodicity" slicer to achieve the filter on periodicity ( Monthly, Weekly, Daily) and then select second slicer with corresponding value of the first ( Day, Month, Week).
Your code is very clear for the Monthly period, but I would like to know for "Weekly" ( Hebdo in screenshot) ,  so I need to make it as a numeric value (like YYYY-WW) in second slicer "Period", in order to be able to select the week ?

Thank you.

Best regards, 

[v-jtian-msft]

Hi,@Randcharles .Thanks for your reply.
I am glad to see that the case I gave can be of help.

Yes your understanding is very correct, in fact I understand your idea of continuous filtering of data through two slicers, the first slicer selects the time granularity, the second slicer selects the specific time (year, month, day or week) but if the specific time field as long as it is a text type can not be filtered directly through the text value of the slicer continuous filtering (the data is cut off), fortunately, your original Fortunately, there is a time and date column in your original data table, this column is the key to continuous filtering, in short, all you need to do is to correctly get the real time value in the text time slicer, and then according to the real time value, in the dax code written through the table itself exists in the [date] column to realize the continuous filtering of data!

For your weekly granularity, according to what you said you can convert it to “YYYY-WW” such a format, this is a very good idea, in fact, the key to the problem is to change the format of the screening conditions: will not be able to realize the continuous selection of the text into a continuous screening of the date or number.
Wish you realize your needs soon!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jtian-msft · Accepted solution]

Hi,@Randcharles I am glad to help you.
According to your description, you want to realize that when you select the value of the slicer, the visual actually shows the data of the three months before this point in time, for example, if you select "2024 - May", it will return the data of March, April, and May of 2024, instead of only displaying the data of May!
First of all, I would like to answer your questions first: According to the screenshot you provided, your slicer is similar to: "month of the English language" such as "May", or "2024S26" can be clearly seen slicer options are text type, the problem arises here!
power BI has no way to continuously display and select non-date or non-numeric data in visual (including the slicer). power BI automatically treats each text type value as a single value, so the conclusion is: if you are using the slicer for a text type field, you can't achieve continuous filtering directly from the slicer!
like this.
For date/time types or date types or numeric types, the slicer formatting choices are as follows

For the text type slicer, the choice of formats is very small (and it is not possible to filter the data continuously, the filtered data can not be used as a continuous interval)

Luckily, you can achieve continuous filtering without changing your current text slicer

1. You need to write a measure, and then use the value of the measure to implement continuous filtering.
2. The date filter in the measure can not be a text type, you need to change the intercepted date to a normal data or datatime type by string interception.

Here is my test
Test data.

You need to recreate a separate table for displaying the slicer data.
like this.

Based on the actual data, create a measure that filters out the data for the first three months of the selected date.
will select the date of the first three months of data marked as 1, and then filter the region only filter and display the results of measure = 1

 Here is my specific code:

Currently select "2023-04", return to the first day of the selected month three months ago: 2023/2/1

Currently select "2023-04", return to the last day of the selected month: 2023/4/30
Mark the visual data in these two time ranges as 1 and the rest as 0.

M_result = 
VAR _selectDate= MAX('slicer_1'[YearMonth])
VAR _position = FIND("-", _selectDate, 1)
VAR _month = RIGHT(_selectDate, LEN(_selectDate) - _position)
VAR _year =LEFT(_selectDate,4)
VAR _monthNum=INT(VALUE(_month))
VAR _yearNum=INT(VALUE(_year))
//get the firstday of the selectedmonth
VAR _actualDate=DATE(_yearNum,_monthNum,1)
//get the firstday  three months ago
VAR _3preMonthDate=EOMONTH(_actualDate,-3)+1
//get the lastday selected
VAR _lastMonthDate=EOMONTH(_actualDate,0)
//Get the real date in the actual calendar table
VAR _date=MAX('Table'[Date])
RETURN 
IF( _date >= _3preMonthDate&& _date <= _lastMonthDate,
1,0)

Then you can select "2024-04" and display the data of the first three months of the month in visual.

Note that you need to write the code according to the actual situation, suitable for your own computing environment, because the writing of the measure will be affected by different computing environment, display different values, so I hope that you can through the advice I provide the correct writing of the measure, to display the desired results!
If you can provide a pbix file that does not contain sensitive data and share it on the forum via OneDrive, it would be helpful to solve your problem.
I wish you the best of luck in finding a suitable solution soon.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.