Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
Solved

Help with Accumulated

How are you, I hope you can help me... I have the requirement to make a cumulative over time, I have a table of activities that relates directly to the calendar table by the date of the activity. It'...
  • amitchandak's avatar
    amitchandak
    2 years ago

    Syndicate_Admin , If you need cumulative with date/calendar table, use meausre like

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

    Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

    Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

     

    Use Column date table in measure, visual and slicer

     

    If you need the last-day value for a simple sum

     

    Cumm Sales = CALCULATE(lastnonblankvalue('Date'[Date]), SUM(Sales[Sales Amount])) ,filter(allselected(date),date[date] <=max(date[Date])))

amitchandak's avatar
amitchandak
Super User
2 years ago

Syndicate_Admin , If you need cumulative with date/calendar table, use meausre like

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

 

Use Column date table in measure, visual and slicer

 

If you need the last-day value for a simple sum

 

Cumm Sales = CALCULATE(lastnonblankvalue('Date'[Date]), SUM(Sales[Sales Amount])) ,filter(allselected(date),date[date] <=max(date[Date])))