Forum Discussion

Martin_Leneman's avatar
Martin_Leneman
Regular Visitor
5 years ago
Solved

Need help with incorrect totals

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!

Need Help
  • Martin_Leneman's avatar
    Martin_Leneman
    5 years ago

    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! 

5 Replies

  • Martin_Leneman's avatar
    Martin_Leneman
    Regular Visitor
    5 years ago

    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's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi Martin_Leneman ，

       

      Also thanks for your sharing.

       

      Best Regards,
      Kelly

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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Martin_Leneman , Try return like below. with addcolumns

     

     

    return

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

    • Martin_Leneman's avatar
      Martin_Leneman
      Regular Visitor
      5 years ago

      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's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        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!