Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Accumulating total in matrix?

Hi, does anyone know if the below is achieveable and if so, how to do it? I have a matrix visual which has a measure for payments (sum(payments)) and the month/year of the payment.

What I want to do is add the previous month payment to the current month so I can see by month, the figure increasing so the last month would be the total paid to date.

So with the below example, we got paid 38,470.21 in Jan 2017. In February we got paid 293,648,65 so in February I would want it to show as 332118 and then in March it would show 589110 and so on.

Can this be done?

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Please check the formula.

    measure = SUMX(FILTER(ALLSELECTED('table'),[year] = selectedvalue([year])&&[month]<=max([month])),[payment])

     

    Best Regards,

    Jay

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , If you need YTD, with help from the date table. Year and mOnth in visual should come from date table

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

     

    Cumm

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

     

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Please check the formula.

    measure = SUMX(FILTER(ALLSELECTED('table'),[year] = selectedvalue([year])&&[month]<=max([month])),[payment])

     

    Best Regards,

    Jay