Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I think I have a very simple question but I can't figure it out, I have a table with purchase and sales with stock quantity. I want to create a separate column that calculates the daily stock value. But how can I get a Total stock result like the column "Total Stock Good" in my example, I just created that to makes things clear.
With kind regards,
Björn
Solved! Go to Solution.
There may be other solutions, but I'd get cumulative for both purchase and sales qty and then subtract cumulative sales from cumulative purchase. The following measure should work:
My Measure =
VAR __SalesRunningTotal =
CALCULATE (
SUM ( 'financials'[ Sales] ),
FILTER (
ALLSELECTED ( 'financials'[Date] ),
ISONORAFTER ( 'financials'[Date], MAX ( 'financials'[Date] ), DESC )
)
)
VAR __DiscountRunningTotal =
CALCULATE (
SUM ( 'financials'[Discounts] ),
FILTER (
ALLSELECTED ( 'financials'[Date] ),
ISONORAFTER ( 'financials'[Date], MAX ( 'financials'[Date] ), DESC )
)
)
RETURN
__SalesRunningTotal - __DiscountRunningTotal
Using power bi sample data
Please give it a thumbs up if this helps!
@bkoenen you would need an index column and then you can either do a calulcated column like this
Column =
VAR _purchase =
SUMX (
FILTER (
'Table',
'Table'[Index] <= EARLIER ( 'Table'[Index] )
&& 'Table'[Article] = EARLIER ( 'Table'[Article] )
),
'Table'[Purchase QTY]
)
VAR _sales =
SUMX (
FILTER (
'Table',
'Table'[Index] <= EARLIER ( 'Table'[Index] )
&& 'Table'[Article] = EARLIER ( 'Table'[Article] )
),
'Table'[Sales QTY ]
)
RETURN
_purchase - _sales
or a measure like this
Measure =
VAR _filter =
FILTER (
ALL ( 'Table' ),
'Table'[Index] <= MAX ( 'Table'[Index] )
&& 'Table'[Article] = MAX ( 'Table'[Article] )
)
VAR _purchase =
CALCULATE ( SUM ( 'Table'[Purchase QTY] ), _filter )
VAR _sales =
CALCULATE ( SUM ( 'Table'[Sales QTY ] ), _filter )
RETURN
_purchase - _sales
Thank you for the code, I copied it and it works.
Kind regards Björn
There may be other solutions, but I'd get cumulative for both purchase and sales qty and then subtract cumulative sales from cumulative purchase. The following measure should work:
My Measure =
VAR __SalesRunningTotal =
CALCULATE (
SUM ( 'financials'[ Sales] ),
FILTER (
ALLSELECTED ( 'financials'[Date] ),
ISONORAFTER ( 'financials'[Date], MAX ( 'financials'[Date] ), DESC )
)
)
VAR __DiscountRunningTotal =
CALCULATE (
SUM ( 'financials'[Discounts] ),
FILTER (
ALLSELECTED ( 'financials'[Date] ),
ISONORAFTER ( 'financials'[Date], MAX ( 'financials'[Date] ), DESC )
)
)
RETURN
__SalesRunningTotal - __DiscountRunningTotal
Using power bi sample data
Please give it a thumbs up if this helps!
@bkoenen , Try a new columns
Sumx(filter(sheet, [Article] = earlier([Article]) && [delivery Date] <= earlier([delivery Date])),[Purchase Qty] - [Sales Qty])