MTD SUM BY EACH WEEK IN MONTH

[cartercjb]

Hello Sisense Community!
I have the following draft of a column chart:

There is a dashboard filter = last month (Jan. 2022). This is currently showing a sum value for each week based on this formula:

(SUM([METRIC]),[Weeks in Date])

 My goal is to maintain the same weekly view, but instead, calculate a MTD sum view for each week. This would look like the following screenshot I made in Excel:

Any suggestions on the best way to accomplish this?

Thanks!

-Carter

[Angelina_QBeeQ]

Hi @cartercjb !
You could use RSUM() function for accumulating your measure by the sorting order of the dimension.

RSUM((SUM([METRIC]),[Weeks in Date]))

[cartercjb]

Thank you, @Angelina_QBeeQ !! What if I were to flip this around just slightly..  

So I have a KPI that is showing a MTD sum. Below that I would like to display a trending microchart that shows the same MTD sum calculation, but from 7 days ago. So it would essentially be a MTD sum from last week. Any suggestions on that? THANK YOU!

@harikm007 

[Angelina_QBeeQ]

Try to use PastWeek  function inside RSUM like this:

RSUM(PastWeek([Total Sales Value]))

[cartercjb]

@Angelina_QBeeQ @harikm007 so I was able to get the microchart to correctly sum each week by changing the date dimension from month to week:

However, now I can't get the overall/main red KPI to show the total MTD value. It is showing the first week's value: 

when I am seeking the last week's value (1734):  

Is this able to be accomplished?