Running Total

[pbi_m1]

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?

[pbi_m1 · Accepted solution]

Thanks Daniel. That's exactly solving it and way easier than CROSSJOIN.

[v-danhe-msft]

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

[pbi_m1]

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,