How to add a running total line chart on a bar chart

[Ashish_Mathur]

Hi,

Follow these steps:

1. Create a Calendar Table by going to Modelling > New Table and writing this formula: Calendar = CALENDAR(MIN(Table[Trade Date]),MAX(Table[Trade Date]))
2. In the Calendar Table, write this calculated column formula to extract Year from the Date: Year = Year(Calendar[Date])
3. Build a relationship from the Trade Date column to the Date column of the Calendar Table
4. To your visual, drag Year from the Calendar Table.
5. Write these measures

Volume traded = SUM(Table[Trade volume])

Volume traded till date = CALCULATE([Volume Traded],DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar]),Calendar[Date]),MAX(Calendar[Date])))

Hope this helps.

[Cmcmahan]

If date and trade volume are in the same table, this should work.  Why are you using raw_crd_allocations[Trade Date] in your example instead of Table[Trade Date]?

If they're in different tables you may need to change your filter to something like

FILTER(ALL(table), YEAR(table[Trade Date]) <= YEAR(MAX(RELATED(raw_crd_allocations[Trade Date])))

[Anonymous]

upupup

[richbenmintz]

Hi @Anonymous ,

I am guessing that 2015 is not the start year for sales you need to set a lower boundary in your filter like

Measure = CALCULATE(SUM(table[Trade Volume]),
                                          FILTER(ALL(table),
YEAR(table[Trade Date]) >= YEAR(MIN(raw_crd_allocations[Trade Date])) &&
                                               YEAR(table[Trade Date]) <= YEAR(MAX(raw_crd_allocations[Trade Date]))))

[Anonymous]

Table should be "table" not "raw.crd.allocations". Sorry for mixing them... It is still the original number. Not sure why....

[Anonymous]

For example for 2015, the cumulated value should be 0.39T not 1.3T... It is confusing

[Cmcmahan]

Real quick, could you create a new calculated column with this portion of your code?

COLUMN = VALUES(table[Trade Volume]), FILTER(ALL(table), YEAR(table[Trade Date]) <= YEAR(MAX(table[TradeDate])))

I'm curious to see what values it IS using in the SUM, if it isn't using the right values.

Is this graph being filtered in another way?  Say by some category?  If so, the ALL(table) might be removing those filters and giving a full total instead of just the subset this graph is for.  You can use ALL(table[column]) to just remove those filters:

FILTER(ALL(table[Trade Date]), YEAR(table[Trade Date]) <= YEAR(MAX(table[Trade Date]))

[Anonymous]

It is much larger than the cumulated value...

[Anonymous]

Raw data in table in database is something like:

Trade Date     Trade Volume

1/6/2015         435222.4512

1/4/2015        232224.2243

3/6/2016       132345.3333

........                     ......