Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Planned Ending Inventory (3 future Months for current month)

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:

rvacha83_0-1609193675553.png

 

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

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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
    )
)

sales.png

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.

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

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
    )
)

sales.png

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
Super User
Super User

@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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.