How to create a visual listing items in a point in time

[mart1fio]

I have a data set where items come in and then out of a specific location. I want to create a visual where a user can select a date and get a list of all of the 4 items from the table that were in that location on that date as well as some general counts, sums. Here a a mock up of the data I have;

Item	Amount	Date In	Date Out
A	$             20.00	01/07/2022	02/07/2022
B	$             21.00	31/06/2022	27/07/2022
C	$           789.00	15/07/2022	17/07/2022
D	$        9,052.00	10/07/2022	01/08/2022
E	$             41.00	27/07/2022	29/07/2022
F	$                3.00	31/06/2022	24/07/2022
G	$             92.00	31/07/2022	02/08/2022
H	$           567.00	01/07/2022	16/07/2022

If my user wants to know which items we had in this location on the 17/07/2022 I want to be able to display all items in a grid and calculate the total amount. I would use a grid for the list and a card to display the distinct count of Items on that day and another for the total dollar amount for those items.

Number of Items   Total Dollar Amount

           4                       $9895

17/07/2022
Item	Amount	Date In	Date Out
B	$             21.00	31/06/2022	27/07/2022
C	$           789.00	15/07/2022	17/07/2022
D	$       9,052.00	10/07/2022	01/08/2022
F	$                3.00	31/06/2022	24/07/2022

My data will be in a single table and will be the only data in this report.

[v-cgao-msft · Accepted solution]

Hi @mart1fio，

Please try:

Number of Items = 
VAR _slicer = MAX('Calendar'[Date])
VAR _count = COUNTROWS(FILTER('Table','Table'[Date In]<=_slicer&&'Table'[Date Out]>=_slicer))
RETURN
_count

Total Dollar Amount = 
VAR _slicer = MAX('Calendar'[Date])
VAR _total = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Date In]<=_slicer&&'Table'[Date Out]>=_slicer))
RETURN
_total

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[mart1fio]

Thanks, I find myself with only May 2021 version of Power BI Report Server (our on prem version we use instead of Desktop) so I cannot open your attached file as yet. I have logged a support ticket to get it updated so hopefully will be able to view it in the file as well soon.

I have managed to reproduce the formula in my table and it worked thanks.