Forum Discussion

Icon for Helper II rank

Helper II

5 years ago

filtered dCalendar

Hi, guys. It is possible to create a filtered table, so that it is an auxiliary dCalendar, with a date range according to the date chosen by the user? For example, in the graph below, I would like ...

Icon for Community Champion rank

Community Champion

5 years ago

Can't you use the regular date slicer set to "Between"?

You will need a Calendar Table for that though

Icon for Helper II rank

Helper II

5 years ago

Hi, Paul, the intention is for the user to choose only one date.

PaulDBrown
Community Champion
5 years ago
...and the measure calculates what date range?
- STEPH0018
  Helper II
  5 years ago
  SELECTEDVALUE(dCalendario[DATA]) and
  LASTDATE(PARALLELPERIOD(dCalendario[DATA],-1, YEAR))
  - PaulDBrown
    Community Champion
    5 years ago
    
    Do you want to see the data for the last year based on the date selected?
    
    See if this works for you.
    
    1) Duplicate you date table using (I've called the duplicate "Period Filter") new table in the ribbon and
    
    Period Filter = 'Calendar Table'
    
    Join this table to the Calendar table in a1-to many inactive relationship with a single filter direction from the Period Filter Table to the Calendar Table. The model should look like this:
    
    Now add the slicer from the calendar table, and create the visual using the date field from the Period filter table as the axis.
    
    Create a measure along these lines:
    
    Data by sel date = VAR CalD = MAX('Calendar Table'[Date]) VAR SelPeriods = DATESINPERIOD('Period Filter'[Date], CalD, -1, YEAR) RETURN CALCULATE([Sum of Sales], REMOVEFILTERS('Calendar Table'), KEEPFILTERS(SelPeriods), USERELATIONSHIP('Calendar Table'[Date], 'Period Filter'[Date]))
    
    Add this measure to the visual and you will get:

Related content