Hi I've created a formula to calculate the OH as of 1 YR ago today.
But I would like to find a way to figure out the OH as of any date I enter into a cell or select from a drop down (Calendar table)
What the current formula does is take OH now + units sold in the last 364 days, - whats been received in last 364 days - transfers in + transfers out.
1YR AGO ON HAND TOTAL:=CALCULATE(tblInvSkuMaster[ON HAND + IN TRAN TOTAL]-'tblInvSkuMaster'[UNITS RECEIVED LAST YEAR]+tblInvSkuMaster[UNITS SOLD LAST YEAR])
UNITS RECEIVED LAST YEAR:=CALCULATE('tblInvSkuMaster'[RECEIVED TOTAL UNITS],DATESBETWEEN(dCalendar[DATES],TODAY()-364,TODAY()))
UNITS SOLD LAST YEAR:=CALCULATE('tblInvSkuMaster'[SALES TOTAL UNITS SOLD],DATESBETWEEN(dCalendar[DATES],TODAY()-364,TODAY()))
Thanks
But I would like to find a way to figure out the OH as of any date I enter into a cell or select from a drop down (Calendar table)
What the current formula does is take OH now + units sold in the last 364 days, - whats been received in last 364 days - transfers in + transfers out.
1YR AGO ON HAND TOTAL:=CALCULATE(tblInvSkuMaster[ON HAND + IN TRAN TOTAL]-'tblInvSkuMaster'[UNITS RECEIVED LAST YEAR]+tblInvSkuMaster[UNITS SOLD LAST YEAR])
UNITS RECEIVED LAST YEAR:=CALCULATE('tblInvSkuMaster'[RECEIVED TOTAL UNITS],DATESBETWEEN(dCalendar[DATES],TODAY()-364,TODAY()))
UNITS SOLD LAST YEAR:=CALCULATE('tblInvSkuMaster'[SALES TOTAL UNITS SOLD],DATESBETWEEN(dCalendar[DATES],TODAY()-364,TODAY()))
Thanks