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

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?

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

        Anonymous 
        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