Power BI DAX Rollover Function In Matrix Needs to Factor for Negatives

[marksaba20]

I have an Inventory Walk table that has month on columns and values on rows. Rows consist of Stock on Hand, Total Receipts, Total Demand, and Net Inventory.

Stock On Hand is always the current month, then Total Receipts and Total Demand for each month. Net Inventory is a measure that is Stock On Hand + Total Receipts - Total Demand. Using the below function, I was able to successfully build out the walk, allowing it to roll by month.

Net Inventory WALK =
VAR _t =
ADDCOLUMNS(
    WINDOW(
        1,
        ABS,
        0,
        REL,
        ALL('Date Table'[Month Yr Sorted],'Date Table'[Sort]),
        ORDERBY('Date Table'[Sort],ASC)
    ),
    "@currentmonth",
    CALCULATE(
        [Stock On Hand],
        KEEPFILTERS('Date Table'[End of Month]=EOMONTH(TODAY(),0))
    ),
    "@receipt",[Total Receipts],
    "@demand",[Total Demand]
)
RETURN
SUMX(_t,[@currentmonth]+[@receipt]-[@demand])

In the image linked below, I have shown what this Net Inventory WALK function returns AND below that, I showcased what I would prefer it to look like. In the Desired Net Inventory WALK, when a column returns a negative value for Net Inventory WALK, I want it to return as a ZERO. I then want the beginning Stock on Hand for the following month to be a ZERO, instead of negative.

ImageOfTable

For reference, the Stock on Hand WALK function is as shown below, which takes the Net Inventory WALK value from the previous month. Any reference in the function of [Stock on Hand] is the firm current value of stock on hand.

Stock on Hand WALK =
VAR _t =
ADDCOLUMNS(
    WINDOW(
        1,
        ABS,
        -1,
        REL,
        ALL('Date Table'[Month Yr Sorted],'Date Table'[Sort]),
        ORDERBY('Date Table'[Sort],ASC)
    ),
    "@currentmonth",
    CALCULATE(
        [Stock On Hand],
        KEEPFILTERS('Date Table'[End of Month]=EOMONTH(TODAY(),0))
    ),
    "@receipt",[Total Receipts],
    "@demand",[Total Orders]
)
RETURN
IF(SELECTEDVALUE('Date Table'[End of Month])=EOMONTH(TODAY(),0),
CALCULATE(
    [Stock On Hand],
    KEEPFILTERS('Date Table'[End of Month]=EOMONTH(TODAY(),0))
),
SUMX(_t,[@currentmonth]+[@receipt]-[@demand])
)

Any help would be appreciated! Thank you!

 

[Matt Allington]

I can't help without seeing the semantic model structure and understanding the data, but I can give you some advice. I assume you have a TSQL background; the solution you have written is from the perspective of someone writing database queries. This is not how DAX is designed to work. In DAX, you should build the structure of the output using visuals and then write measures that react to the visual to present the numbers you want to see. You should first build a solid semantic model (star schema, ideally) before starting.

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Power BI DAX Rollover Function In Matrix Needs to Factor for Negatives

I have an Inventory Walk table that has month on columns and values on rows. Rows consist of Stock on Hand, Total Receipts, Total Demand, and Net Inventory. Stock On Hand is always the current month, then Total Receipts and Total Demand for each month. Net Inventory is a measure that is Stock...

chandoo.org

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[marksaba20]

I can't help without seeing the semantic model structure and understanding the data, but I can give you some advice. I assume you have a TSQL background; the solution you have written is from the perspective of someone writing database queries. This is not how DAX is designed to work. In DAX, you should build the structure of the output using visuals and then write measures that react to the visual to present the numbers you want to see. You should first build a solid semantic model (star schema, ideally) before starting.

How would you suggest I build this Net Inventory Walk function in PBI otherwise? Needs to be DAX because Im combining tables with DAX as the reference table for the measure.