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