Measure revenue before a date set by a slicer

[Berl21]

Hi,

I am trying to get revenue sums displayed as to how they were before a certain date. Users should be able to use a slicer and select manually the date they want to analyze.

To do so, I created a slicer with my date table and chose the option "before". I also connected the date table to my fact table.

After that, I wrote the following measure:

Max Test Revenue =

VAR _selecteddate= MAX(Datetable[Date])

return

CALCULATE('Opportunity History'[Test sum revenue], FILTER('Opportunity History',_selecteddate))

This measure stays as a blank.

I also tried writing it with treatas:

VAR _selecteddate= MAX(Datetable[Date])
return

CALCULATE(Opportunity History'[Test sum revenue], TREATAS({_selecteddate},Datetable[Date]))

When using the latter, I see absolutely no changes when selecting different dates in the slicer.

Any idea how I can solve this?

Thanks in advance!

[Greg_Deckler · Accepted solution]

@Berl21 Try:

Max Test Revenue =
VAR _selecteddate= MAX(Datetable[Date])
return
SUMX(FILTER(ALL('Opportunity History'),'Opportunity History'[Date] <= _selecteddate),'Opportunity History'[Test sum revenue])