Filter a slicer with the date range

[TaariqHussain]

Good day

I need help filtering the following data.

I have 3 sets of data in which i track for differences as per below.

We have cashiers that drop money from their tills into the drop safe, the cashiers drop the physical cash into the drop safe which counts the cash and puts it into a bag which is in the safe, and than prints a slip with the bag number and the amount of cash dropped in,  the cashier than takes the slip and inputs the details of the drop onto our point of sales system: Bag number and amount dropped. there are multiple drops into the single bag until its full, the money than gets deposited into our bank account when picked up and the reference in our bank account is the bag number as well. so the point of reference i use to track the money dropped is the bag numbers,

I have 3 sets of data which has the same data: which is bag numbers date dropped and amounts

the 3 data sets is the Drop safe data, our point of sales data, as well as bank account the bank account us different as it shows the total amount received for the full bag which essentially should agree to the sum of all the drops.

Now my visual i use measures to track the data is the sum of all the drops from the drop safe portal less the sum of all the drops tracked on our point of sales system which should net off to 0 if all was captured correctly.

and the next one is the sum off all the drops on the drop safe portal less the amount received in the bank account which should net off to 0( this is to make sure that the drop safe provided paid us correctly)

 i set a relationship between the 3 tables using the bag number and all 3 dates columns to a calendar,

Please advise on how i can get it to work in the following way:

If i set the date filter for lets say relative date to the current week, i want a separate filter( I want to use a chiclet slicer) to show the bag numbers applicable to the current week only where i am able to click on the bag number and it filters the visual based on that bag only

I will post set of sample data below

[v-yiruan-msft]

Hi @TaariqHussain ,

What's your expected result? Do you want to get the difference between sum of drop safe data and point of sale data?  Besides that, you also want to get the difference between sum of drop safe data and bank account? Am I right? Could you please explain more about your expected result based on the shared sample data? It would be helpful to find the solution. Thank you.

By the way,  you also created a calendar table. And created the relationship with the above 3 tables. Could you please describe the backend scenario for these date fields? Thank you.

Best Regards

[TaariqHussain · Accepted solution]

Apologies for the delayed response, simple solution i used a duplicate table the drop safe data as my main table that shows all bag numbers and removed all duplicate bag numbers from it, i also kept the date column in and removed the rest of the columns from the table so it only had date and bag number, i than added those bag numbers to my chiclet slicer for selection and used a relative date filter to show bags for the last 7 days only