Forum Discussion

mart1fio's avatar
mart1fio
Frequent Visitor
3 years ago
Solved

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

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 th...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
mart1fio
Frequent Visitor
3 years ago

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.