Forum Discussion

JMAlloway's avatar
JMAlloway
Helper I
5 years ago

Help with Cumulative Average in Date Based Matrix

I've been struggling with finding a solution to this problem. I have a calculated column that I want to track the cumulative average by each new month this year. For example, in a matrix listed with January-December rows, I want the values to be cumulative for all previous years plus January in the 'January' row, through February in the 'February' row, etc. The image below shows the calculated column by each month, but its breaking down the calculation by its relative month for every year.  What I want is, in this case, only January to contain a value, and February-December to be blank.

 

 

 

Thanks for any info on this issue!

 

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    JMAlloway , You have to create measure like these with help from Date table (prefer)

    example

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

     

    Only Year Cumulative

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    JMAlloway 

    If you want to track the cumulative average by each new month this year, you can add a visual-level Year filter to this matrix and set value is 2021. I am not sure when you want the cumulative average, what should be the denominator to divide the cumulative total? Assume the denominator is cumulative number of months, you can create measures like below to achieve the results.

    Cumulative Total = CALCULATE(SUM(Tbl[Count]),FILTER(ALL('Date'[Date]),'Date'[Date]<=MAX('Date'[Date])))
    Cumulative Months = DATEDIFF(MINX(ALL('Tbl'[Date]),Tbl[Date]),MAX('Date'[Date]),MONTH)+1
    Cumulative Average = IF(ISBLANK(SUM(Tbl[Count])),BLANK(),DIVIDE([Cumulative Total],[Cumulative Months]))

    If you only want to show the Cumulative Average column in the matrix, click the down arrow next to Month field and check Show items with no data. Here is a PBIX file for your reference.

     

    Regards,
    Community Support Team _ Jing Zhang
    If this post helps, please consider Accept it as the solution to help other members find it.