Slicer with dynamic current date based on selected country

[korrigan]

I need to set up a slicer allowing date selection using either "current Date" label or prior dates, from a calendar table.

On the report, there is another slicer allowing selection of a country.

Upon sleection of a country, the date associated with "current date"may be different: For instance, if the user selects USA, the "current date" should return data for the 2021-03-20. However, if he selects Australia, it should be 2021-03-21.

My first idea was to create additional columns in my calendar table, one per country, to get "current date"on the correct record of the calendar table, depending on the timezone. This works fine but I can't switch from one column to another in DAX as column don't support dynamic value based on a slicer.

I have the feeling that I'm not approaching the problem correctly,

Thanks

[v-alq-msft]

Hi, @korrigan 

You may create a table with a utc time difference column for each country. Then you could get the utc time with UTCTODAY or UTCNOW  and get the converted time. Finally you need to create a calculated column in date table like below. Hope it helps.

x =
IF (
    [Date] = [Converted Date],
    "Current Date",
    IF ( [Date] < [Convert Date], "Prior Date" )
)

Best Regards

Allan

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

[Anonymous]

HI @korrigan 

You need to create proper interactions between your slicers.

Below video will help you achieve it.