0 Replies Latest reply: Sep 17, 2015 8:19 AM by Michal Polak RSS

    Expression for summing up a stock prices in pivot table to a date

      Hello everyone,

       

      at first, I am not sure how to name this topic to be precise with my problem, but I will try to explain it in the simpliest way even I consider this to be very challenging problem.

       

      Im developing stock app. And I need to see stock price for goods per month/week. To get a stock price I have to sum all stock prices to a specific date.

      This is not a problem with this expression:

      =SUM({<id_typZbozi={1}, [Data.Datum]={'<=$(vObratkaMesicKS)'}>}[Data.Skladova cena celkem])

      where variable $(vObratkaMesicKS) represents a date to which stock prices of sold good have to sum up...

      [Data.Skladova cena celkem] = stock price


      But now I need for management make a pivot table like this:

      And I have no idea how to roll (sum up) stock price to every month of the year without the variable because the ending date of summing should be the last day of every month.

       

      Please, guys help! I am desperate...

       

      PS: Another idea was to make a new table in script with pre-summed data (grouped by good, stock, week, month and year). But this would make synthetic keys to my model! So I dont like this solution...

       

      Thank you!