topic Re: Summing up monthly values in the line chart in DAX Commands and Tips

Summing up monthly values in the line chart

LovePowerBi1994 — Wed, 24 Aug 2022 17:12:37 GMT

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

Re: Summing up monthly values in the line chart

tamerj1 — Wed, 24 Aug 2022 17:46:38 GMT

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

Re: Summing up monthly values in the line chart

LovePowerBi1994 — Thu, 25 Aug 2022 06:52:00 GMT

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

Re: Summing up monthly values in the line chart

tamerj1 — Thu, 25 Aug 2022 07:38:50 GMT

Hi LovePowerBi1994

Bestand is a measure right?

Re: Summing up monthly values in the line chart

LovePowerBi1994 — Thu, 25 Aug 2022 07:39:47 GMT

yes exactly. It means Stock.

Re: Summing up monthly values in the line chart

tamerj1 — Thu, 25 Aug 2022 07:46:19 GMT

LovePowerBi1994

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

Re: Summing up monthly values in the line chart

LovePowerBi1994 — Thu, 25 Aug 2022 07:51:00 GMT

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 )

Re: Summing up monthly values in the line chart

LovePowerBi1994 — Thu, 25 Aug 2022 07:55:40 GMT

Formula of the measure "Bestand"

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

Re: Summing up monthly values in the line chart

tamerj1 — Thu, 25 Aug 2022 07:56:10 GMT

LovePowerBi1994

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

Re: Summing up monthly values in the line chart

LovePowerBi1994 — Thu, 25 Aug 2022 08:10:11 GMT

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!

Re: Summing up monthly values in the line chart

tamerj1 — Thu, 25 Aug 2022 08:19:25 GMT

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

Re: Summing up monthly values in the line chart

LovePowerBi1994 — Thu, 25 Aug 2022 09:08:00 GMT

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!