Forum Discussion
compounding values for dates
Hi all, hope someone can help me with my query. I'm a PowerBI newbie so nay help would be great. My manager has a spreadsheet that he wishes me to duplicate within Power BI. This spreadsheet's first tab has £ value data for dates for each stock item i.e.
1/6/2017
stock 1 £2345.98
stock 2 £1234.11
This part of the spreadsheet I can easily replicate in Power BI. But, the next tab then performs a calcuation with this data:
1/6/2017 2/6/2017
Stock 1 £2345.98 value of 1/6/2017 + value of 2/6/2017
Essentially for the value cell for stock 1 for 2/6/2017 it is taking the stock 1 value cell from the first tab and adding it to the value for 2/6/2017 on the first tab.
I am struggling to replicate this calculation (compounding of the data) within Power BI. Can anyone help? The data I am able to show is what is imported and replicates the first tab without the compounding element.
Many thanks.
Bimster,
Firstly, unpivot your columns in your table, rename the blank column to "Stock".
Secondly, create a column using the DAX below.
Column = CALCULATE(SUM(Table[Value]),ALLEXCEPT(Table,Table[Stock]),Table[Attribute]<=EARLIER(Table[Attribute]))
Thirdly, create a Matrix visual as shown in the following capture.
Regards,
Lydia
4 Replies
Hi Bimster,
I assumed that your table is in a modular format with Date - Stock - Value (and you don't have a column for each date in your source table, if you do I would advise you to unpivot the columns to this format.
Add the measure below:
Stock_Value = VAR min_date = MIN ( Stocks[Date] ) RETURN CALCULATE ( SUM ( Stocks[Value] ), Stocks[Date] = min_date ) + CALCULATE ( SUM ( Stocks[Value] ), Stocks[Date] = min_date - 1 )This should give you what you want, so stock value is current date value + previous day value per stock.
Regards,
MFelix
Hi MFelix,
The formula is good but I don't think it's quite right for what I need. The data for 03/06/2017 should be (for each stock) the figure calculated for 02/06/2017 (i.e. sum of 01/06/2017's value and 02/06/2017's value) added to the figure for 03/06/2017
01/06/2017 02/06/2017 03/06/2017 Stock 1 10 30 53 Stock 2 15 25 34 Bimster,
Firstly, unpivot your columns in your table, rename the blank column to "Stock".
Secondly, create a column using the DAX below.
Column = CALCULATE(SUM(Table[Value]),ALLEXCEPT(Table,Table[Stock]),Table[Attribute]<=EARLIER(Table[Attribute]))
Thirdly, create a Matrix visual as shown in the following capture.
Regards,
Lydia
