Date Slicer and Measure

[Anonymous]

Hi All, 

Can't seem to find a solution and have tried many ways. I have the following measure: 

Remaining Shares Unvested =
CALCULATE (
SUM( 'Equity'[Share Splits] ),
'EquityFull'[New Vest Date] >= DATE (2019, 4, 1)
)

What I want to do is to create a date range slicer based on the NEW VEST DATE field that will effect ONLY this measure here. I essenitally want it to sum the Share Splits column based on a date range. 

I can't seem to get it to work. 

Any help would be great please. Thanks!!

[MarkLaf]

Besides what I think you've already articulated, I'm guessing your issue is you also need to "cancel" the existing relationship between Equity and EquityFull, such that the new table your slicer is on is the only thing filtering the measure.

Full procedure:

1. New Table in model (you need to pull over the foreign key for Equity and the Vest Date column - if unsure, just make a full copy of EquityFull with TableForSlicer = 'EquityFull'):
   

   TableForSlicer = 
   SELECTCOLUMNS(
      'EquityFull',
      "Equity Table's Foreign Key",[EquityID or whatever it's called],
      "Date",[New Vest Date]
   )

2. Set up same relationship between Equity and TableForSlicer that you have between Equity and EquityFull, and make it inactive.
3. Now build measure, ignoring relationship betwen Equity and EquityFull and explicitly calling relationship between Equity and this new table that will be driving the slicer:
   

   Remaining Shares Unvested =
   CALCULATE (
      SUM( 'Equity'[Share Splits] ),
      ALL('EquityFull'),  //i.e. ignore any other filters/slicers you may have on EquityFull
      USERELATIONSHIP(
        'Equity'[ID or whatever key is called],
        'TableForSlicer'[Equity Table's Foreign Key]
      )
   )

   You can add additional built-in filters on TableForSlicer if needed, like the >= DATE (2019, 4, 1) one used before.
4. Now you can add a date slicer with 'TableForSlicer'[New Vest Date], and that measure will be sliced by it and nothing else.

[parry2k]

@Anonymous Create another date table and use that for slicer and then from that date table, use date range in your measure. let me know if you need further assistance on this.

[Anonymous]

Hi @parry2k,

I know I can create a date table from the new vest date field, but it's the logic I can't get to work to sum dynamically within that measure. 

[parry2k · Accepted solution]

@Anonymous let's assume your date table is called "Calendar". Try following measure and tweak as per your need.

myMeasure = 
VAR startDate = MIN(Calendar[Date])
VAR endDate = MAX(Calendar[Date])
RETURN

CALCULATE (
SUM( 'Equity'[Share Splits] ),
FILTER( EquityFull,
'EquityFull'[New Vest Date] >= startDate &&
'EquityFull'[New Vest Date] <= endDate

)

[Anonymous]

That worked. Thanks. 🙂