Can you accomplish this in PowerBI:

[Robbief]

I'm not certain this is even possible, or within the realms of PowerBI; but I know this is the right place to ask!

I have a stock movements table that looks like this:

It has various types of stock movements from Stocktakes, through to Deliveries and Sales, Etc.....   Every Stock Movement creates a stock line, and the running total is written to the line at that time.

Our users would like to select a specific Date / Time, and have the system return the total value of stock on hand based on their input.  I have deduced that the following logic would give the correct result:

1. A User to define Date on which they would like the Total Stock on Hand results returned
2. Set the User Defined Date as the "End Date"
3. Using a given input date, return the closest Stocktake date prior to the date
4. Set the closest stocktake date prior to that date as the "Start Date"
5. Return a sumarised Table showing the most recent Movement Records for each distinct Item Between the User Defined Date (End Date), and the Stocktake Date  (Start Date)
6. Result to deliver a table including Item, Running Totals & Value.

Based on the above, and a user input parameter of: 09/03/2021 22:00; I would expect the resultant table to look like this::

I'm fairly new to PowerBI, so have a few questions:

1. Can PowerBI accomplish this?  (Returning / Filtering a Table from a user input Paramter)
2. How would you do this in the PowerBI UX?
3. Is this a DAX or a M Query type question?
4. Any hints as to how I might accomplish this would be greatly welcomed!!!

Regards

Robbie

[amitchandak]

@Robbief , Create measure like below example , for running total, value , movement type. If needed, then for date too.

Use an independent data table , if possible for filter

running total =
var __MAX = maxx(allselected(Date), Date[Date])
VAR __id = MAX ('Table'[ITEM] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[ITEM] = __id,'Table'[Date] <= __MAX )
CALCULATE ( Sum ('Table'[running total] ), VALUES ('Table'[ITEM] ),'Table'[ITEM] = __id,'Table'[Date] = __date,'Table'[Date] <= __MAX )