Hi,
I am new to this forum and relatively new to PowerBI.
I have a request to report the flow of goods in quantities in a Power BI report.
So, shelf stock at a start date + purchases from that date - sales from that date = shelf stock at end date.
That's not a big issue apart from the calculation of the shelf stock at the beginning. We use the Report Server version of Power BI with imported data. That data holds a field that has the change date of the stock. The user must be able to change the start and end date in the report with a slicer. To calculate the shelf stock at the start date all transactions before that date have to be summed up and presented in a column in the report. To do this I have to capture the start date the user picks. This start date comes from a calendar table that is joined with the change date in the data set.
To accomplish this, I have put a slicer that holds the date from the calendar table. I use the slicer setting "after" to have only one date in the slicer (but it can also be a "between" range). This one date should be the start date. Next, I added a table with all the relevant columns and with the Edit Interactions button I changed the table not being filtered with this start date when the user changes the start date in the slicer. The report should not be filtered with that start date but instead all transactions before that start date have to be summed in one figure per stock item.
To capture the start date I use this dax code:
CALCULATE(MIN(Calendar[Date].[Date]); ALLSELECTED('Calendar'))
That works fine, but when adding this date to a measure that filters all transactions before that date (so the shelf stock on start date) I am lost. No matter what I do, there is no sum of quantities of all the transactions before that date. It appears that the start date that is being captured and shown correctly in the report (with the card visual) is not used in the sum-formula. Even not when I use a variable in the DAX formula or use a calculated column. In the formula it appears that the start date falls back to the first date in the calendar table, which is 2010-01-01. I can give all measures I have tested for far, but what should be the correct approach for this?
Hope someone can give me a hint.
