cancel
Showing results for 
Search instead for 
Did you mean: 

MTD SUM BY EACH WEEK IN MONTH

cartercjb
10 - ETL
10 - ETL

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

cartercjb_0-1644379500321.png

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:

cartercjb_1-1644379824611.png

Any suggestions on the best way to accomplish this?

Thanks!

-Carter

4 REPLIES 4

Angelina
9 - Travel Pro
9 - Travel Pro

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

 

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

 

 

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

cartercjb_0-1645117917370.png

 

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 

Try to use PastWeek  function inside RSUM like this:

RSUM(PastWeek([Total Sales Value]))

Angelina_1-1645174708250.png

 

 

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

cartercjb_0-1645507569449.png

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: 

cartercjb_1-1645507971008.png

 

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

cartercjb_2-1645507984512.png

Is this able to be accomplished?