Forum Discussion
Running Total
Hi
I have been going through 10+ 'Running Total' entries and haven't found a solution to this one yet, so excuse if this is already solved.
| DATE | PROD_ID | VALUE |
| 2-Jul-18 | A111 | 3 |
| 3-Jul-18 | A111 | 0 |
| 4-Jul-18 | A111 | 2 |
| 5-Jul-18 | B222 | 1 |
| 6-Jul-18 | B222 | 0 |
| 9-Jul-18 | B222 | 8 |
The difference to previous problems I looked at is that the date entries for some PROD_ID stop at a certain date and another PROD_ID will come in from then on e.g.I'd like to create the running total of the VALUE column of a table with the 3 columns: DATE, PROD_ID, VALUE
Based on this I would like the Running Total Matrix visualisation to be showing
| DATE | A111 | B222 |
| 2-Jul-18 | 3 | 0 |
| 3-Jul-18 | 3 | 0 |
| 4-Jul-18 | 5 | 0 |
| 5-Jul-18 | 5 | 1 |
| 6-Jul-18 | 5 | 1 |
| 9-Jul-18 | 5 | 9 |
However, at the moment I get (i.e. the Running Total for A111 stops on 4-Jul-2018)
| DATE | A111 | B222 |
| 2-Jul-18 | 3 | 0 |
| 3-Jul-18 | 3 | 0 |
| 4-Jul-18 | 5 | 0 |
| 5-Jul-18 | 1 | |
| 6-Jul-18 | 1 | |
| 9-Jul-18 | 9 |
using the often mentioned measure formula + selecting PROD_ID in the 'Columns' section of the 'Matrix' visualisation:
RunningTotal = CALCULATE (
SUM ( Table[VALUE] ),
FILTER (
ALL ( Table[DATE] ),
Table[DATE] <= MAX ( Table[DATE] )
)
)
In order to get there in SQL Server I would CROSS JOIN the DATE & PROD_ID, then LEFT JOIN the VALUE column from the Table and use the SUM OVER PARTITION to create the Running Total.
I was wondering what the easiest way is to achieve this in Power BI?
Thanks Daniel. That's exactly solving it and way easier than CROSSJOIN.
3 Replies
Hi v-danhe-msft,
Thanks for the prompt reply. Could it be that you responded to a different submission. I wasn't necessarily interested in the total value by month.
My main question was how to extend a running total column down for all dates in a table beyond the last date enty of e.g. PROD_ID = 'A111'. I have been invetigating the CROSSJOIN and LOOKUPVALUE functions, however, there are seem to be a few ways to build it and I'm still looking for how to do it most efficiently given CROSSJOIN (VALUES(Table[DATE]), VALUES(Table[PROD_ID])) can create quit a large subtable. I also realised that I can't create any Relationships between the CROSSJOIN subtable and the Table.
Any comments are welcome,
Hi pbi_m1,
Based on my test, you can refer to below steps:
1.I have entered some sample data to test for your problem like the picture below:
2.Create a new table and a new measure.
Table 2 = DISTINCT(Table1[PROD_ID])
Measure = CALCULATE(SUM('Table1'[VALUE]),FILTER(ALL('Table1'),'Table1'[DATE]<=MAX('Table1'[DATE])&&MAX('Table 2'[PROD_ID])='Table1'[PROD_ID]))
3.Create a Table visual and add the related columns, now you can see the result.
You can also download the PBIX file to have a view.
https://www.dropbox.com/s/yitavv5yxuxt418/Running%20Total.pbix?dl=0
Regards,
Daniel He
Thanks Daniel. That's exactly solving it and way easier than CROSSJOIN.
