CODEWITHSUNDEEP
powerbipowerbi-desktoppowerbi-custom-visuals
chris
chris

Reputation: 135

Linking PowerBI custom date slicer?

I'm trying to use a custom date slicer in Power BI that acts like the relative date slicer, but using NOW() instead of TODAY() for it's calculations, as all our data is stored in EST and the relative date slicer operates in UTC. I've been able to create a slicer with a drop down for each day range, and add calculated columns on the dataset that contain a column for each day range it should be included for (1 or 0, with 1 indicating it should be included). That part is working properly, and rows are getting tagged properly.

Like so: Table view of main table

I have a slicer table with the Range Names, which match the "Last X days" 1/0 column names on the main data set table.

I'm having trouble figuring out how to apply the slicer to the main table dataset though, in such a way that when the range is selected, only those rows are included in the report. I've tried it a number of ways, but I'm having trouble getting the report view to match what I see when I filter in table view. What would be the appropriate way to allow the drop down to filter the main table on a set of different columns like that?

Upvotes: 0

Views: 46

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

The NOW and TODAY DAX functions are indifferent to UTCNOW or UTCTODAY when published and executed in Power BI Service. PBI Service always uses UTC.

You mentioned your data is stored in EST, does the data switch to EDT in the summer or is it always UTC-5 consistently all year round? If it remains the same, then one suggestion is you create a new column for a UTC Date of your data table (add 5 hours) and then use the native relative date slicer on this new column. Simplest option.

Otherwise, the other option is to use a Measure filter.
In your "date table" that you are using for your slicer, add a new numeric column of number of days, this will be used in the DAX measure. For example, your date slicer table could look like:

Slicer Days
Last 1 Day 1
Last 2 Days 2
Last 7 Days 7
Last 30 Days 3
Last 60 Days 60
Last 90 Days 90
Last 120 Days 120

(Side note, you could drop your sort column and use the Days column for sort.)

Then create a new Measure similar to:

Slicer Date Filter =
  var slicerDays = MAX('Date Slicer'[Days])
  var timeNowEST = UTCNOW() - 5/24
  var fromDate = timeNowEST - slicerDays
  var resultNoSlicer = COUNTROWS('YourDataTable')
  var resultWithSlicer = CALCULATE(COUNTROWS('YourDataTable'), YourDataTable[DateTime] >= fromDate)
  return IF( ISFILTERED('Date Slicer'), resultWithSlicer, resultNoSlicer)

Lastly, add this new Measure as Visual Filter to each visual you want to filter and set it to is not blank.

With this option, you don't need the calculated columns in your data table.

Upvotes: 0

Related Questions