Before and After using One Date

[RonHall]

I have a table called "Combined" with a column for "Date".  The table is a list of activities.  I have built a dates table using dax and set it up as a sicer. It is called "DatesTable" and the column with the dates is called "SelectedDate"  I want to be able to select a date from this drop down and then with two measures show the activites 90 days before this date (Before) and 90 days after this date (After).

If I hard code the dates into the mesaure it works just fine ...

Before = CALCULATE(COUNTROWS(Combined), DATESBETWEEN(Combined[Date], Date(2017,1,1), Date(2017,3,31)))

After2 = CALCULATE(COUNTROWS(Combined), DATESBETWEEN(Combined[Date], Date(2016,10,1), Date(2017,1,1)))

But when I try to use the dates from the drop down it doesn't.

Before = CALCULATE(COUNTROWS(Combined), DATESBETWEEN(Combined[Date], 'Dates Table'[SelectedDate], 'Dates Table'[SelectedDate]+90))

After = CALCULATE(COUNTROWS(Combined), DATESBETWEEN(Combined[Date], 'Dates Table'[SelectedDate], 'Dates Table'[SelectedDate]-90))

I tried making the dates from the dates table into measures and dropping them in but that didnt work either.  Any suggestions?

[MFelix]

Hi @RonHall,

According to what I understand you want to calculate the Before and After based on a slicer you made with the SelectedDate, to make this you need to know what is the selected date so in the dates table make this measure:

DateSelected = Max('Dates Table'[SelectedTable])

Now use this measure in your Before and after should look something like this:

Before = CALCULATE(COUNTROWS(Combined), DATESBETWEEN(Combined[Date], [DateSelected], [DateSelected]+90))

After = CALCULATE(COUNTROWS(Combined), DATESBETWEEN(Combined[Date], [DateSelected], [DateSelected]-90))

Regards

MFelix

[RonHall]

Thank you for the input, the measures work and the dates change when I select a new date from the drop down, but the bar chart where I have Before and After doesn't.  It shows one set of data and does not change when I change the date in the drop down.

[RonHall]

Actually it is changing, but the values for before and after are exactly the same every time.

[RonHall · Accepted solution]

I figured it out, when I changed my date selector to the date within the combined table it works. I had it as my date table previously.