Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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 |
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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
User | Count |
---|---|
97 | |
88 | |
76 | |
70 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |