Display data less than slicer date

[Anonymous]

HI,

I Need to show , how many tickets are open as on report date (slicer date) (count all open ticket less than slicer date). Find open ticket logic below,

Table 

TicektID	Created Date	Completed Date
1	01-Jan-19	02-Jan-19
2	01-Jan-19	03-Feb-19
3	05-Feb-19	08-Feb-19
4	06-Feb-19
5	08-Feb-19

* No date in completed date column for not completed ticket.

My Model

Output

Expected Result

Open ticket Logic/ formula : 

CreateDate<@SlicerDate and CompletedDate>@Slicerdate
* We stored not completed date as "9999-12-31" fro Null value

Please help me on it thanks

[amitchandak]

You can create a calculated formula with a filter clause to get the filter on two different columns. 

Test TWO filter Sales = ( 
VAR _Cuur_start = Generator[Report Start Date]
VAR _Curr_END = Generator[Report End Date]
return 
calculate(sum(Sales[Sales]),filter(sales,Sales[Order_Date] >= _Cuur_start && Sales[Delivery Date] <=  _Curr_END ))
)

In case, it does not work well with joined time dim, use this example to have independent date slicer  

https://medium.com/chandakamit/power-bi-comparing-data-across-date-ranges-36be49b68613

[Anonymous]

Thanks For your reply,

I can display the measure (count) , but i cant display the underlying data in table.

like Ticket id, Ticket name, tickt status, created date. etc...

in table the data filtered by month(slicer date and month)

but i want display all data created date less than <slicer date. (Even the data created 2 years back and open status)

[amitchandak]

In the newer version you have filter pane in older version this there are page options. Page and  Report Level filter. There add your date and in advance filter, you have before option, Hope this will work for your need

[Anonymous]

Yes, thanks

but i want to apply this value from slicer(selected value)

the filter pane it is allow satic value right?

[amitchandak]

Not very clear, what you say in the last update. Once you apply any value on filter pane, it will filter values in tables related to that table.  The filter pane allows you to choose from a calendar and it will be available to users.