cumulative data base on earlier dates with client filter based on slicer

[mhendel]

Hello,

First sorry for my English which is not my mother tongue.

I have a huge table containing 3 columns : Client reference code, monthly costs and monthly date (first day of the month).

This table contains the data for all clients

 In the other hand, i have a slicer which i can use to choose a client reference code.

So i can use a Line Chart visual to represents data monthly for one client.

My problem is the following : i need to visualize cumulative costs monthly, by summing the costs of the previous months, based on the client reference code (=slicer) , to obtain something like that : 

I am completely stucked...Someone could help me ?

Thanks in advance, 

Michael 

[az38 · Accepted solution]

Hi @mhendel 

create a column like

Cumulative Sum = 
CALCULATE(
SUM(Table[monthly costs]),
FILTER(ALL(Table), Table[monthly date] <= EARLIER(Table[monthly date]) && Table[Client reference code] = EARLIER(Table[Client reference code]) )
)

and use it as chart Y-axis value

[mhendel]

Hi @az38 ,

It is almost what i want, but it remains one problem : 

 Sometimes, i have no data for one month, and so i wanted my curve to continue to be horizontal when no data is present for that month.  You can see the difference between the two plots.  I dont know if i explain well....

Any hint?  Thanks for your help,

Michael

[az38]

Hi @mhendel 

for your new demand you should use a little bit more complicated data model

you need to add a Calendar table, like

Calendar Table = CALENDAR(MIN(Table[monthly date]), MAX(Table[monthly date]))

then create a relationships between calendar table and your table and put Calendar Table[Date] field on the X-axis

[mhendel]

@az38 Thanks for your quick answer.

My problem is i have in the same table mutliple projects (project code) with several dates for each project...Do i have to split the table by project?  Any hint?

Regards?

[az38]

@mhendel 
no, it should work with regards to filter context

if you will not choose any project it will aggregated by all table.  If you will enable filter by project, it should be filtered 

[mhendel]

Thanks a lot!!!