Summing up monthly values in the line chart

[LovePowerBi1994]

Hello dear community,

I would like to sum up past values in a line chart in order to be able to calculate the stock level. Unfortunately, I can't get the value from the previous months to add up.

As you can see, the stock in May should be 89 (stock from April) - 126 sales + 68 returns (not shown in the picture) = 31 and not -126.

I hope you understand my problem.

I have already tried several formulas and just don't know what to do. Many thanks in advance for your support.

LG

[LovePowerBi1994]

yes exactly. It means Stock. 

[tamerj1]

Hi @LovePowerBi1994 

Are all columns coming from the same table? If not would you please advise the the details of your data model and relationships?

In case you are using a date table please try

Stock =
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR T1 =
    ADDCOLUMNS (
        ALLSELECTED ( 'Date'[YearMonth] ),
        "@Sales", CALCULATE ( SUM ( TableName[Sales] ) ),
        "@Goods Received", CALCULATE ( SUM ( TableName[Goods Received] ) )
    )
VAR T2 =
    FILTER ( T1, 'Date'[Date] <= CurrentDate )
RETURN
    SUMX ( T2, [@Goods Received] - [@Sales] )

Thank you

[LovePowerBi1994]

Hi @tamerj1 

thanks for the quick reply.
In total, the information comes from 4 tables.

There is one time table
A table with goods receipt information.
A table with sales and returns.
and a table with shortages.
To illustrate this, I have attached a brief outline of the data model.

The formula is:

Goods Receipts - Sales + Returns - Shortages.


I have tried to adapt the formula. Unfortunately, I was only shown the current stock across all months and not the stock that was valid for the respective month.

Enclosed is my attempt at adjustment. I have already created a measure for calculating the stock.

Stock =

VAR CurrentDate =

MAX ( vw_Zeit[Date] )

VAR T1 =

ADDCOLUMNS (

ALLSELECTED ( vw_Zeit[Date] ),

"@Sales", CALCULATE ( [Bestand]) )

VAR T2 =

FILTER ( T1, vw_Zeit[Date] <= CurrentDate )

RETURN

SUMX ( T2, [Bestand] )Data Modelproblem

[tamerj1]

Hi @LovePowerBi1994 

Bestand is a measure right? 

[tamerj1]

@LovePowerBi1994 

Please try instead of RETURN SUMX..., RETURN COUNTROWS ( T2 )

[LovePowerBi1994]

Formula of the measure "Bestand" 

=

sum(goods recieved)-CALCULATE(sum(Sales),Filter(Sales,ReturnYesorNo="No"))+[Returns]-sum(shortages)

[LovePowerBi1994]

like this? 

Stock =

VAR CurrentDate =

MAX ( vw_Zeit[Date] )

VAR T1 =

ADDCOLUMNS (

ALLSELECTED ( vw_Zeit[Date] ),

"@Sales", CALCULATE ( [Bestand]) )

VAR T2 =

FILTER ( T1, vw_Zeit[Date] <= CurrentDate )

RETURN

COUNTROWS ( T2 )

[tamerj1]

@LovePowerBi1994 

Which column are you using in the x-axis? Please use the Month-Year column from the date table. 

[LovePowerBi1994]

it works! thank you very much! I took the first formula you mentioned and adjusted the x axis with MonthYear.
Now I just have to find a way to sort the x axis correctly.

Thank you thank you thank you!

[tamerj1 · Accepted solution]

Hi @LovePowerBi1994 
Please try this measure

Stock =
VAR CurrentDate =
    MAX ( vw_Zeit[Date] )
VAR T1 =
    ADDCOLUMNS (
        ALLSELECTED ( vw_Zeit[Date] ),
        "@Sales", CALCULATE ( SUM ( Sales[Sales] ), Sales[ReturnYesorNo] = "No" ),
        "@GoodsRecieved", CALCULATE ( SUM ( 'goods receipt'[Goods Recieved] ) ),
        "@Returns", [Returns],
        --assume [Returns] is a measure?
        "@Shortages", CALCULATE ( SUM ( Shortages[quantity] ) )
    )
VAR T2 =
    FILTER ( T1, vw_Zeit[Date] <= CurrentDate )
RETURN
    SUMX ( T2, [@GoodsRecieved] - [@Sales] + [@Returns] - [@Shortages] )

If you are working at Month-Year level the following would be much more efficient

Stock =
VAR CurrentDate =
    MAX ( vw_Zeit[Date] )
VAR T1 =
    ADDCOLUMNS (
        ALLSELECTED ( vw_Zeit[Month-Year] ),
        "@Sales", CALCULATE ( SUM ( Sales[Sales] ), Sales[ReturnYesorNo] = "No" ),
        "@GoodsRecieved", CALCULATE ( SUM ( 'goods receipt'[Goods Recieved] ) ),
        "@Returns", [Returns],
        --assume [Returns] is a measure?
        "@Shortages", CALCULATE ( SUM ( Shortages[quantity] ) )
    )
VAR T2 =
    FILTER ( T1, vw_Zeit[Date] <= CurrentDate )
RETURN
    SUMX ( T2, [@GoodsRecieved] - [@Sales] + [@Returns] - [@Shortages] )

[LovePowerBi1994]

with the first part i get the same results. 

The second one diddnt worked and caused an error. 

Stock 2 =

VAR CurrentDate =

MAX ( vw_Zeit[Date] )

VAR T1 =

ADDCOLUMNS (

ALLSELECTED ( Zeit[MonthYear]),

"@Sales", FMWawiLoUmsatz[Abverkaufsmenge], --is a measure

"@GoodsRecieved", CALCULATE ( SUM (FMWawiLoArtikelWe[WE Summe] ) ),

"@Returns", [Retourenmenge],

--is a measure

"@Shortages", CALCULATE ( SUM (FMWawiLoArtikelAbbuchungen[MengeSumme]) )

)

VAR T2 =

FILTER ( T1, Zeit[MonthYear] <= CurrentDate )

RETURN

SUMX ( T2, [@GoodsRecieved] - [@Sales] + [@Returns] - [@Shortages] )

but the first Solution works fine for me. Thank you!!! you helped me a lot!