CODEWITHSUNDEEP
powerbidax
Phil C.
Phil C.

Reputation: 31

Dynamic date ranged table

Hope you are fine. I have a table in power bi that has a date column and some data in the rest. In this fashion:

| DateDt |ColumnA |ColumnB | etc |

I have a a slicer linked to that date column. What I am trying to do is to generate a chart that shows the data of a column 15 days before and 15 days after the selected date. So, whenever the user changes a value in the DateDt Slicer the chart shows the new interval.

I figured I could do a separate table using dax. This is so far what I have done:

30DaySelectedDateTable = 
 
VAR SelectedDate = SELECTEDVALUE(ForecastAndActual[DateDt])
VAR Plus15Days = SelectedDate + 15
VAR Minus15Days = SelectedDate - 15

RETURN
FILTER(
    ALL(ForecastAndActual),
    SelectedDate >= Minus15Days && SelectedDate < Plus15Days
)

But is not behaving as expected, since is giving me an empty data frame. Any idea?

Thanks in advance

Upvotes: 0

Views: 172

Answers (2)

Marcus
Marcus

Reputation: 4015

Your particular use case is not possible (and the existing answers are wrong), since calculated DAX tables are not dynamically updated. They are only calculated exactly once: at refresh time, NOT at query time.

What you need to do depends on your use-case, but it needs to be based on measures, which is the only dynamically updating calculation form in Power BI. A measure would look like this, for example:

Stuff = 
VAR _selectedDate = SELECTEDVALUE ( 'Date'[Date] )
RETURN
CALCULATE ( 
    [Some other measure] , 
    REMOVEFILTERS ( 'Date' ) , 
    DATESBETWEEN ( 'Date'[Date] , _selectedDate - 15, _selectedDate + 14 )
)

However the full solution depends on what you want to do. In other words: It appears this is a classical XY-problem, where your question pertains to some limitation in how you have decided to solve your problem, instead of asking how your problem should be solved

Upvotes: 0

AHSAN UL HAQ
AHSAN UL HAQ

Reputation: 77

30DaySelectedDateTable = 
 
VAR SelectedDate = SELECTEDVALUE(ForecastAndActual[DateDt])
VAR Plus15Days = SelectedDate + 15
VAR Minus15Days = SelectedDate - 15

RETURN
FILTER(
    ALL(ForecastAndActual),
    SelectedDate >= Minus15Days && SelectedDate < Plus15Days
)

your code is correct but few step you need to apply to run this logic

  1. Remove filter interaction over the chart (for this you will select slicer and update filter interaction setting for particular chart where you want to look level of detail)
  2. use Single Selected Date through Slicer if not then in DAX either using

VAR SelectedDate = SELECTEDVALUE(ForecastAndActual[DateDt])

to

VAR SelectedDate = MIN(ForecastAndActual[DateDt])

hope it work for you .

Upvotes: 0

Related Questions