Cumulative values from Specific Date

[adk79]

Hello , 

Let's say I have a Forecast table with the following data

Date	Sales
05/2022	10
06/2022	20
07/2022	30
08/2022	40
09/2022	50
10/2022	60

I need to create a table graph that will always start cumulating the values from M-1 (based on today's date) and put whatever is  before it as 0 like below  : 

Date	Cumulated Values
05/2022	0
06/2022	0
07/2022	0
08/2022	40
09/2022	90
10/2022	150

How can I achieve this ?

Thanks in advance,

Best Regards,

[v-rzhou-msft · Accepted solution]

Hi @adk79 ,

Here I create a sample to have a test and I think you can try below code to create a measure.

Sample:

Measure:

Measure = 
VAR _START = EOMONTH(TODAY(),-2)+1
RETURN
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Date]>=_START&&'Table'[Date]<=MAX('Table'[Date])))+0

 Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adk79]

Thank you @v-rzhou-msft , it worked perfectly. I really appreciate it

[TypimWuerfel]

Hi @adk79,

I see two issues there.

First one to solve is the date filter. I used a variable, where I filtered the Sales by TodayM-1.
Second issue is much easier as you can cumulated the variable as any other cumulated sum by filtering allselected dates with isonorafter(Date, max(date), descendants).

I hope this one helps you:

Cumulated Values = 

var SalesfromMminus1 = Calculate(Sum(Sales[Sales]),Filter('Dim Datum','Dim Datum'[DatumBK] >= DATE (YEAR (TODAY()), MONTH ( TODAY () ) - 1, DAY ( TODAY () - 1 ) )))
return
CALCULATE(
	SalesfromMminus1,
	FILTER(
		ALLSELECTED('Dim Datum'[DatumBK]),
		ISONORAFTER('Dim Datum'[DatumBK], MAX('Dim Datum'[DatumBK]), DESC)
	)
)

____________________________

You want more helpful tipps and tricks like that?
Please subscribe: https://www.youtube.com/channel/UC2lAgCgfyLCHsRv0h-ETBWQ 
 

[adk79]

Hello @TypimWuerfel 

Thank you for your reply. However, the measure doesn't work and it returns the same value as the non cumulated. Please note that my measure that I want to comulate is as follows (I don't know if this changes anything) : 

CALCULATE([Value],PARALLELPERIOD('D_Calendar'[Date],1,MONTH))