Forum Discussion
Display data less than slicer date
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
5 Replies
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
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)
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
