Date Picker slicer with relative date

[george1]

Hello, I need one help. @RossEdwards , @visheshjain , @collinq , @MiquelPBI , @mariussve1 

I have data like this, here I have  4 different date options:

Now without changing the style of the data I would like to create a date picker option like this,

Here user can pick the option the would like and based on this selection they will get relative date filter,

And then they can filter the data and shows only one field in the table view, whaever the charges associated with it with selected date,

How can I achieve that? If use picked 2 months in relative date slicer and change the date option to Post date then I would table to display 2 months of data of Post date. Can someone please help on how can I achieve this? Please and thanks.

[MiquelPBI]

I believe you can accomplish this with a combination of measures and a field parameter.  This is what the model would look like. 

1. Create relationships between a calendar table and the three dates in a fact table.  Note, one relationship will be active and two will be inactive.  Note the image of model above.   

2. Write measures for each date column you want to calculate.  To my previous point you'll write measures to look at the inactive relationships. That said, measures you write will respect the active relationship unless you tell the measure to look at the inactive.

3. Create a field parameter that uses the measures you created.  Field parameters are a wonderful technique to create dynamic displays and filters.  This table is disconnected in the model.  Note the model image at beginning of post.

4. Drop the date column from your calendar table in slicer for the date slicer. Drop the field parameter column into a slicer.  Drop the same field paramter column in your table visual.  

I hope that helps!

[MiquelPBI]

Yes, the dates will remain as selected and the user can select and show multiple date columns.  

I dont see a way to attach a file.  I'm assuming I'll have to add it to my Git repo and share a link.  I can do that but will have to wait until later. Sorry.  

[george1]

Hi @MiquelPBI , Oh I see. Sure, I can wait and really appriciate your help in this. Once I get the pbix file it will help me to understand little better way.

So in my table there are about 7-8 measures. So do I need to do this for every measures?

[george1]

Hi @MiquelPBI , Is it possible to attach pbix file here? If you can it will help a great deal. 

Do you think with this selection of date does stay when I change one date option to another.

Example: I have selected date of service and then I pick date 6/27/2023. Now if I change date from date of service to Posting date then my date changes to select all. what I want that time is my date 6/27/2023 to stay when I change from Date of Service to Posting Date.

If I change it to relative date then if for example my date selection is last 2 months and I change to Posting date then selection stays to last 2 months but my table view displays all available dates which I don't want. If my relative date slicer says last 2 months than table view also should display last 2 months of data when I change Date of Service to Posting date.

If you can attach pbix file it would be great. Please

[george1]

Hi @DataNinja777 , this looks very interesting. I have a couple of questions:

1) In your code you mentioned create disconnected date table, with below function:

DateTable = CALENDAR(MIN('YourTable'[Encounter Date]), MAX('YourTable'[Coded Date]))

 Why do I need to take only this two fields from the date? Because sometimes Coded Date as max available value and sometimes Date of Service has max value. Then why do I need to take only this two in the mix?

2) I do have many measures, like 7 or 8 measures. So do I need to create this kind (SelectedDateMeasure) for 7 or 8 times?

And also do you have pbix file and if you can attach it here then I can have a look at the reference as well. This will help.

[DataNinja777]

@george1 ,

You can use a field parameter to allow users to select from multiple date columns and apply the selection to a date picker. Here's how you can do it:

1. Enable Field Parameters: First, ensure that field parameters are enabled in Power BI. To do this, go to File > Options and settings > Options > Preview features, and check Field parameters.
2. Create a Field Parameter:
• In the Modeling tab, click New Parameter > Fields.
• Select the date columns you want to include in the parameter: "Encounter Date", "Date of Service", "Post Date", "Coded Date"
• Name the parameter, such as "Date Selector."
3. Use the Field Parameter:
• After creating the field parameter, you'll have a slicer in your report where users can choose which date column they want to filter.
• Create a date picker slicer in Power BI and bind it to the selected date column via the field parameter.

Now, you can switch between different date fields using the parameter, and the selected date column will dynamically apply to the date picker.

Best regards,

[george1]

@DataNinja777 , Thank you so much for the suggestion you provided. I tried this, and it works as you saying but only option I have is, selection of date doesn't stay when I change one date option to another.

Example: I have selected date of service and then I pick date 6/27/2023. Now if I change date from date of service to Posting date then my date changes to select all. what I want that time is my date 6/27/2023 to stay when I change from Date of Service to Posting Date.

If I change it to relative date then if for example my date selection is last 2 months and I change to Posting date then selection stays to last 2 months but my table view displays all available dates which I don't want. If my relative date slicer says last 2 months than table view also should display last 2 months of data when I change Date of Service to Posting date.

Is this something possible? How can I do that? Please help.

[DataNinja777]

Hi @george1 ,

You can achieve the desired output by combining field parameters with a slicer set to the relative date option, as shown below. I have generated sample data and recreated the output you’re looking for.

Even if you have multiple measures, you can use the switch function along with a disconnected table of measures to toggle between them seamlessly.

I have attached an example pbix file for your reference. 

Best regards,

[george1]

Thanks @DataNinja777 , The only problem I see with this is when I change the date then it doesn't stick.

Like here I have selected this options, Now I change dates to last 10 days and it display correctly,

Now if I change the date option here,

It displays all the available dates when I change the date options even though I have selected last 10 days. Is there any workaround to this?

[Anonymous · Accepted solution]

Thanks for the reply from  DataNinja777 and MiquelPBI  , please allow me to provide another insight:

Hi, @george1 

Based on my testing, parameters cannot be converted to the relative time type in a slicer. Therefore, your slicer must be derived from one of the columns in your data table. This is likely why you are encountering errors, as one of your slicers is derived from parameters, while the relative time type is from a fixed column.

I have provided an alternative solution for you:

1.Firstly, create the following measure:

MEASURE = 
VAR diff = 10
VAR selectid =
    MAX ( 'Parameter'[Parameter Order] )
VAR column1d =
    CALCULATE ( MAX ( 'Table'[Column1] ), ALLSELECTED ( 'Table' ) )
VAR column2d =
    CALCULATE ( MAX ( 'Table'[Column2] ), ALLSELECTED ( 'Table' ) )
RETURN
    SWITCH (
        TRUE (),
        selectid = 0,
            IF ( MAX ( 'Table'[Column1] ) >= column1d - diff, 1, 0 ),
        selectid = 1,
            IF ( MAX ( 'Table'[Column2] ) >= column2d - diff, 1, 0 ),
        1
    )

2.Secondly, apply the measure to the visual object:

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

 
Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

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