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). Th...
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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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?