compounding values for dates

[Bimster]

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.

[MFelix]

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

[Bimster]

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

[MFelix]

Try this change in the formula:

Stock_Value =
VAR min_date =
    MIN ( Stocks[Date] )
RETURN
    CALCULATE ( SUM ( Stocks[Value] ), Stocks[Date] <= min_date )
  

Regards,

MFelix

[v-yuezhe-msft · Accepted solution]

@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