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 

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