Forum Discussion

Bimster's avatar
Bimster
Frequent Visitor
9 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

MFelix's avatar
MFelix
Super User
9 years ago

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's avatar
Bimster
Frequent Visitor
9 years ago

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/201702/06/201703/06/2017
Stock 1103053
Stock 2152534
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    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