month to month sales

[Alexbienvenue]

Hoping that someone can help provide insight into the sales table I am trying to create. To preface, this table is based on inventory because its easier for me to pull inventory month to month versus sales. Im essentially subtracting the current months inventory from the previous months inventory to generate the amount of sales we did. Here is where I currently am:

As you can see, my probem is the first and last month. I did manage to make some headway on the first month as seen here:

That second measure does take care of the first month, but obviously for some reason it screws up the ability for the sales column to calculate correctly. Why does that happen?

For the last column, ive tried a few things but I cant write anything that seems to get it to ignore the total inventory if theres a blank. Ive mostly been attempting IF statements.

Just for full disclosure the total inventory column is simply 

SUM('Bottle Inventory'[Amount])

And Sales column is 

= [Previous Month Inventory] - 'Bottle Inventory'[Total Inventory]

Any advice?

[v-cazheng-msft · Accepted solution]

Hi @Alexbienvenue ,

You may try this solution. Here are the Measures used.

Previous Month Inventory =
VAR PMI =
    CALCULATE (
        SUM ( 'Bottle Inventory'[Amount] ),
        PREVIOUSMONTH ( 'Date Table'[Date] )
    )
RETURN
    PMI

LastMonth = 
VAR midT =
    SUMMARIZE (
        'Date Table',
        'Date Table'[Date].[Year],
        'Date Table'[Date].[Month],
        "val", [Previous Month Inventory]
    )
RETURN
    SUMX ( midT, [val] )

Sales =
VAR diff = 'Bottle Inventory'[LastMonth] - 'Bottle Inventory'[Total Inventory]
RETURN
    IF ( ISBLANK ( [LastMonth] ), BLANK (), diff )

Then the result will look like this.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Whitewater100]

Hi:

How does this approach look?https://drive.google.com/file/d/1a5um1tKpQQESMisnumhzTR0tAeNorFRj/view?usp=sharing 

I beleive this EndInv2 was the final measure from last one. Here is the visual with measures below (file has all)

Prev Month End Inv = CALCULATE([End Inventory 2], PREVIOUSMONTH(Dates[Date]))

Sales =

var invdiff =[End Inventory 2] - [Prev Month End Inv]

return

IF(invdiff <0,ABS(invdiff),BLANK())

Here's the end inv measure:

End Inventory 2 =

var currdate = MAX('Dates'[Date])

var lastKnownInventory =

CALCULATETABLE(

SUMMARIZE(

Inventory,

Inventory[Store],

Inventory[Product],

"EndInv",

LASTNONBLANKVALUE(

'Dates'[Date],

SUM(Inventory[Qty])

)

),

FILTER(

ALL('Dates'),

'Dates'[Date]<=currdate

)

)

return SUMX(lastKnownInventory, [EndInv])

[amitchandak]

@Alexbienvenue , do you have an inventory snapshot or do you need build inventory ?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Usually we build like

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

[Alexbienvenue]

This is a sample of how my inventory is set up, one SKU is displayed