Beginning Balance Dynamic Measure

[Anonymous]

Hi all, I posted this before but Microsoft's site won't let me unmark it as solved.

I am pulling in a table called 'pbi TableInventTransStagingV1' - let's just call in the Inventory Transactions table.  This table contains all inventory transactions and has a field called Quantity and a Date Field called DATEFINANCIAL.  What I'm trying to do is recreate a report in Dynamics 365 that displays a Beginning Balance, Ending balance and transactions in between.  The Beginning Balance referes to the balance available at the beginning of that day.  

Since our organization went live with Dynamics 365 on 11/12/2017, that Beginning Balance field is made up of all transactions (Quantity) after 11/11/2017 and before whatever date (DATEFINANCIAL) is selected.  So what I'm trying to do is have a slicer at the top for DATEFINANCIAL, and create a measure that displays the total Quantity where the DATEFINANCIAL is greater than 11/11/2017 and less than the minimum of the dates selected in the slicer.  

Any idea on how to get to this solution?

[masonlee01]

You want to use SELECTEDVALUE function in DAX to grab the value of that slicer.  I would try dynamically creating a calendar table starting at 11/17/2017 to max of the date present in your inventory table.  Then tie the slicer to that.  If you use your regular date table, it might mess up other parts of the report because it could filter the dataset to all dates in that range.  Here is an example on how to make a calendar table - you can remove a bunch of columns you don't want.

calendar = ADDCOLUMNS(CALENDAR(MIN(budget[Beg of Month]), date(2020,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"WeekNum", weeknum([Date], 1),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"WorkingDay", if(or(Weekday([Date])=7, Weekday([Date])=1),FALSE,TRUE),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"MonthDay", FORMAT ([Date], "MM") & "-" & FORMAT ( [Date], "dd"))

Then probably something like...  I'm assuming you have a calendar table called calendar and made a new one as described above called newCalendar and created a slicer on newCalendar[date].

begBal = CALCULATE(SUM(Qty), DATESBETWEEN(calendar[date], DATE(11, 11, 2017), SELECTEDVALUE(newCalendar[date])))