Need all record before selected date from slicer

[vijay_27]

Hello All,
I have two tables:
Calendar Table: Consist of Date Column
Order Table: Consist of Order Date, Order Quantity, Order Number

I have created one slicer with a Date Column from the Calendar Table.
Suppose if I have selected a Date from Slicer say 7th July 2021,
then I want to show all record from Order Table in table visual such that it show all the record before 7th July i.e. Orderdate<Calendardate.
Can you please write the required measure to obtain the result. Please find the screenshot below:

belwo I am sharing the file pbix file:
https://drive.google.com/file/d/1bDvjzb7ctIPzFk9VYoZVC-VqSkR_TRTg/view?usp=sharing

[Ashish_Mathur]

Hi,

These images should help

[vijay_27]

@amitchandak 
suppose in slicer we are using single date say 7 july, now based on this calendar,  i should able to see all records from order table before 7 july. Like order table have three records all this record must be in below table

[v-easonf-msft]

Hi, @vijay_27 

I think you just need to disconnect the relationship between the two tables in your original pbix, because the date filter in the relationship between the tables will conflict with the filter in your measure .

Best Regards,
Community Support Team _ Eason

[vijay_27]

My requirements is that the date must be same to both side of slicer, because it is sync with another page And also I cannot delete relationship  between  two tables because these because im populating other measure on different page

[v-easonf-msft]

Hi, @vijay_27 

If you cannot delete relationship  between  two tables, I think you can copy another ‘Order’ table and use data in this new 'Order' table and 'Calendar' table to create this visual. 

New Order = 'Order'

Measure = 
VAR d =
    ( SELECTEDVALUE ( ( 'Calendar'[Date] ) ) )
RETURN
    IF (
        (
            CALCULATE (
                SUM ( 'New Order'[Order Quantity] ),
                FILTER ( ALL ( 'New Order' ), 'New Order'[Order_Date] < d )
            )
        )
            = BLANK (),
        "select same date on both side of date slicer",
        CALCULATE (
            SUM ( 'New Order'[Order Quantity] ),
            FILTER ( ALL ( 'New Order' ), 'New Order'[Order_Date] < d )
        )
    )

Best Regards,
Community Support Team _ Eason

[vijay_27]

Showing all records, when I select 23rd june but it is showing all records.

All of these solution does not work here

[amitchandak]

@vijay_27 , How many records will be displayed will depend on grouping in the visual table

But you have two options,

Filter using before slicer or use the independent table in the slicer and use a measure that can give data before the selected date. All measures in the table should do that

or you can use one of the measures in the visual level filter and check for non blank

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] <=_max))

[mh2587]

I download your PBIX but there is no data on 7/1/2021

[amitchandak]

@vijay_27 , It is better to use before slicer for that. You have an option in the slicer for that(small down arrow) .

Because if you use a single date an want to display dates more than that, you need independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Date',  'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI