Planned Ending Inventory (3 future Months for current month)

[Anonymous]

Hello,

I am trying to find the right DAX statment for Planned Beginning and Ending inventory. When I manually track this in Excel the formulas are below. But is there a way that I can create this in PowerBI? I currently have the Planned sales and Actual Sales in a matrix visual with the Month, Fiscal year and Items are slicers. 

Beginning Inventory: Current Planned Sales + Next Month's Planned Sales

Ending Inventory: Sum of the Next 3 months Planned Sales

Here is my Matrix:

I know I am missing something just not sure what it is. 

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Not certain what is your data table look like but if you want to create begining and ending inventory based on your description, you can try to create measrues like this:

Beginning Inventory = 
VAR _current =
    SUM ( 'Table'[Planned Sales] )
VAR _next =
    CALCULATE (
        SUM ( 'Table'[Planned Sales] ),
        FILTER (
            ALL ( 'Table' ),
            MONTH ( 'Table'[Months] )
                = MONTH ( SELECTEDVALUE ( 'Table'[Months] ) ) + 1
        )
    )
RETURN
    _current + _next

Ending Inventory = 
CALCULATE (
    SUM ( 'Table'[Planned Sales] ),
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Months] )
            = MONTH ( SELECTEDVALUE ( 'Table'[Months] ) ) + 3
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , With help from time inteligence and date table

measures (example)

MTD Sales = CALCULATE(SUM(Sales[Planned Sales]),DATESMTD('Date'[Date]))
next MTD Sales = CALCULATE(SUM(Sales[Planned Sales]),DATESMTD(dateadd('Date'[Date],1,MONTH)))
next month Sales = CALCULATE(SUM(Sales[Planned Sales]),nextmonth('Date'[Date]))

next three

Ending Inventory = CALCULATE(sum(Sales[Planned Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),3,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Ashish_Mathur]

Hi,

In an MS Excel file, please show your calculations and the expected result.