Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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 

Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    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

6 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks a lot!!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Anonymous 

        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