Slicing with date ranges

[thefal]

I am making a report where the client wants to slice the data using a date slicer. In the slicer, they want to enter a range and have the charts in the report update with data from this new range.

The client wants to see:

- Every entry that has a startdate (called StartTime in the database) within the date range

- Every entry that has a enddate (called StopTime in the databsae) within the date range

- Every entry that was started before the range, but doesn't have an enddate yet.

In other words: every ticket that was opened, closed, or just open the whole time within this date range.

I have imported a table with ticket numbers,  start dates (when was the ticket opened), end dates (when was the ticket closed) and other data.

In a SQL Query, it would look something like this:

  create procedure GetData (@Startdate date,@Enddate date) as
  SELECT *
  FROM [tickets] 
  where 
	(convert(date,StartTime) between @Startdate and @Enddate)
	or (convert(date,StopTime) between @Startdate and @Enddate)
	or (convert(date,StartTime)< @Startdate and StartTime is null)

	exec getdata '2018-02-01','2018-03-01'

However, I have no idea how to implement something like this in Power BI. Is this possible?

[v-yuta-msft]

Hi thefal,

Create a slicer on the date column, then use DAX formula like this:

Result =
CALCULATE ( aggregation, ALLSELECTED ( table[date] ) )

Regards,

Jimmy Tao

[Stachu]

For sure you could apply that filter in the particular KPI itself, something like
CALCULATE(
    [KPI],
    apply filters here
)
what is the structure of your tables and relationships between them? Are you gonna show one or multiple KPIs?

[thefal]

I want to show a few metrics, but also a table with the details.

I have a table with startdate, end date, and a few metrics.

I have kind of solved my problem like this:

- I've made 2 date tables that aren't connected to any other tables, CalStart and CalEnd

- I've made a slicer for each (dropdown)

-I've made 2 metrics called Selected Start and Selected End:

Selected End = lastdate(CalEnd[CalendarDate])
Selected Start = lastdate(CalStart[Calendardate])

-I've added metrics like this:

_WasAlwaysOpen = Calculate(countrows(tickets);
    filter(tickets;
            (tickets[StartDate]<[Selected Start] && (tickets[StopTime] = BLANK() || tickets[StopDate] > [Selected End])) 
           
    ))

Now, when I filter a table using _WasAlwaysOpen is not blank, my table will show the appropriate rows.

This kinda works, except for the fact that I'm using 2 dropdowns instead of 1 date slicer.

Is there any way to improve this? Escpecially because generating the results table with these meaures as filters takes quite long, abount 15 seconds.