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