Forum Discussion

RonHall's avatar
RonHall
Frequent Visitor
9 years ago
Solved

Before and After using One Date

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?

Need Help
  • RonHall's avatar
    RonHall
    9 years ago

    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.

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    9 years ago

    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's avatar
      RonHall
      Frequent Visitor
      9 years ago

      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's avatar
        RonHall
        Frequent Visitor
        9 years ago

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