Need help with incorrect totals

[Martin_Leneman]

Hi all, I could use some help here.. I want to make a calculation regarding stock values changes between months. Here is the scenario.:
Stock values are stored every 1st day of the month. The first day of the month a balance check is made for last months changes. The user selects the last month, I would like to calculate the difference between the two on the fly by altering the month selected within a variable. The row totals are matching up perfectly. The grand totals are not showing, probabely because these are out of the calculation context. 

Here is my formula: 

Stock Value EOM = // stock value End Of Month eq first stock value new month

VAR NextPeriod = IF (SELECTEDVALUE('Appended Sources'[Month]) = 12, 1, SELECTEDVALUE('Appended Sources'[Month]) + 1)

VAR ProdCode = SELECTEDVALUE( 'Appended Sources'[ProductName - Gen] )

VAR Result = CALCULATE(

[StockValue Start of Month] ,

FILTER( ALL ( 'Appended Sources' ), 'Appended Sources'[Month] = NextPeriod),

'Appended Sources'[ProductName - Gen] = ProdCode )

RETURN

Result

Example of the output: 



Tried all sorts of SUMX / SUMMARIZE but can not get it working. The thing is that I also require the totals within other calculations so I could do with your input!

[Martin_Leneman · Accepted solution]

Hi all,
think I got lost in a far to complex approach... After looking at this issue with a fresh mind i figured a simple use of dateadd must do the trick. And sure it did... 

Stock Value EOM DateAdd = CALCULATE( [StockValue Start of Month], DATEADD( 'Date'[Date], +1, MONTH ) )

I use this formula all the time somehow i overcomplicated this  
Thanks for your contributions! 

[v-kelly-msft]

Hi @Martin_Leneman ，

Also thanks for your sharing.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[amitchandak]

@Martin_Leneman , Try return like below. with addcolumns

return

sumx(addcolumns(summarize(Table, Table[Month], Table[Year], Table[Product]), "_1",Result ),[_1])

[Martin_Leneman]

Thanks, unfortunately same result:

Stock Value EOM2 =

VAR NextPeriod = IF (SELECTEDVALUE('Appended Sources'[Month]) = 12, 1, SELECTEDVALUE('Appended Sources'[Month]) + 1)
VAR ProdCode = SELECTEDVALUE( 'Appended Sources'[ProductName - Gen] )

VAR Result = CALCULATE(
[StockValue Start of Month] ,
FILTER( ALL ( 'Appended Sources' ), 'Appended Sources'[Month] = NextPeriod),
'Appended Sources'[ProductName - Gen] = ProdCode )

RETURN
sumx(addcolumns(summarize('Appended Sources', 'Appended Sources'[Month], 'Appended Sources'[Year], 'Appended Sources'[ProductName - Gen]), "_1", Result ), [_1])

[v-kelly-msft]

Hi  @Martin_Leneman ,

Could you provide a sample .pbix file for test?

Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!