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

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
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