Forum Discussion
Funk-E-Guy
Helper II
4 years agoAvoiding Circular Discrepancies for Col A = Col B (last row) and Col B = Col A (current row)?
My goal: To use DAX to calculate Ending Inventory as a function of MAX(0, Beginning Inventory + Receipts - Forecast), for every day and every SKU. My Issue: I do not know how to lookup the previo...
jiaopengzi
3 years agoFrequent Visitor
Here's the information you're looking for.
- In the future, please upload an attachment instead; I spent quite a while copying from it.
- The difficult part is writing the measure of "01_Beginning Inventory". I have provided compatibility for various time dimensions (daily, weekly, monthly, quarterly, semi-annually, and annually) of the measure.
- Note that your "Current Inventory" table should include a date dimension.
- Essentially, there are only inbound and outbound operations. Initial, final, and inventory values are derived from these, so the model should be designed with this logic in mind.
01_Beginning Inventory =
VAR DATE_START0 =
CALCULATE ( FIRSTDATE ( 'Calendar'[dates] ), ALL ( 'Calendar' ) )
VAR DATE_END0 =
LASTDATE ( 'Calendar'[dates] )
VAR TF0 =
HASONEVALUE ( 'Calendar'[dates] ) //日
VAR TF1 =
HASONEVALUE ( 'Calendar'[YearWeek] ) //周
VAR TF2 =
HASONEVALUE ( 'Calendar'[YearMonth] ) //月
VAR TF3 =
HASONEVALUE ( 'Calendar'[YearQuarter] ) //季度
VAR TF4 =
HASONEVALUE ( 'Calendar'[YearHalf] ) //半年度
VAR TF5 =
HASONEVALUE ( 'Calendar'[FY00] ) //年度
VAR N0 =
SWITCH (
TRUE (),
TF0, 0,
TF1,
DATEDIFF (
FIRSTDATE ( 'Calendar'[StartOfWeek] ),
LASTDATE ( 'Calendar'[EndOfWeek] ),
DAY
),
TF2,
DATEDIFF (
FIRSTDATE ( 'Calendar'[StartOfMonth] ),
LASTDATE ( 'Calendar'[EndOfMonth] ),
DAY
),
TF3,
DATEDIFF (
FIRSTDATE ( 'Calendar'[StartOfQuarter] ),
LASTDATE ( 'Calendar'[EndOfQuarter] ),
DAY
),
TF4,
DATEDIFF (
FIRSTDATE ( 'Calendar'[StartOfHalfYear] ),
LASTDATE ( 'Calendar'[EndOfHalfYear] ),
DAY
),
TF5,
DATEDIFF (
FIRSTDATE ( 'Calendar'[StartOfYear] ),
LASTDATE ( 'Calendar'[EndOfYear] ),
DAY
),
0
) + 1
VAR DATE_END1 =
SWITCH (
TRUE (),
TF0, DATEADD ( DATE_END0, - N0, DAY ),
TF1, DATEADD ( LASTDATE ( 'Calendar'[EndOfWeek] ), - N0, DAY ),
TF2, DATEADD ( LASTDATE ( 'Calendar'[EndOfMonth] ), - N0, DAY ),
TF3, DATEADD ( LASTDATE ( 'Calendar'[EndOfQuarter] ), - N0, DAY ),
TF4, DATEADD ( LASTDATE ( 'Calendar'[EndOfHalfYear] ), - N0, DAY ),
TF5, DATEADD ( LASTDATE ( 'Calendar'[EndOfYear] ), - N0, DAY ),
DATE_START0 //无筛选的时,默认日期表期初。
)
VAR DATE_END2 =
IF ( ISBLANK ( DATE_END1 ), DATE_START0, DATE_END1 ) //兼容 dateadd 后的空值,注意日期表的两个端点。
VAR DATE_TABLE0 =
DATESBETWEEN ( 'Calendar'[dates], DATE_START0, DATE_END2 )
VAR IN0 =
CALCULATE ( [02_Receipts], DATE_TABLE0 )
VAR OUT0 =
CALCULATE ( [03_Forecast], DATE_TABLE0 )
RETURN
IF ( [03_Forecast], IN0 - OUT0, BLANK () )
02_Receipts =
SUM ( 'Current Inventory'[Current Inventory] ) + SUM ( 'Receipts'[QTY] ) + 0
02_Receipts_Display =
IF ( [03_Forecast], [02_Receipts], BLANK () )
03_Forecast =
SUM ( Forecast[QTY] )
04_Ending Inventory =
VAR DATE_START0 =
CALCULATE ( FIRSTDATE ( 'Calendar'[dates] ), ALL ( 'Calendar' ) )
VAR DATE_END0 =
LASTDATE ( 'Calendar'[dates] )
VAR DATE_TABLE0 =
DATESBETWEEN ( 'Calendar'[dates], DATE_START0, DATE_END0 )
VAR IN0 =
CALCULATE ( [02_Receipts], DATE_TABLE0 )
VAR OUT0 =
CALCULATE ( [03_Forecast], DATE_TABLE0 )
RETURN
IF ( [03_Forecast], IN0 - OUT0, BLANK () )
Satya2804
3 years agoFrequent Visitor
thank you very much for sharing it.. but in your data example your forecast is always less than (Begining) + Receipts. Lets say your first Forecast is 114 instead of 14. Then the ending will be negative, how can we ensure we dont carry forward the negatives?