Can you accomplish this in PowerBI:

Robbief — Thu, 11 Mar 2021 00:13:16 GMT

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:

A User to define Date on which they would like the Total Stock on Hand results returned
Set the User Defined Date as the "End Date"
Using a given input date, return the closest Stocktake date prior to the date
Set the closest stocktake date prior to that date as the "Start Date"
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)
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:

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

Regards

Robbie

Re: Can you accomplish this in PowerBI:

amitchandak — Thu, 11 Mar 2021 05:31:50 GMT

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 )

topic Can you accomplish this in PowerBI: in DAX Commands and Tips

Can you accomplish this in PowerBI:

Re: Can you accomplish this in PowerBI: