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 ...
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! 

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!