Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Cumulative values from Specific Date

Hello , 

 

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

 

 

DateSales
05/202210
06/202220
07/202230
08/202240
09/202250
10/202260

 

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  : 

 

DateCumulated Values
05/20220
06/20220
07/20220
08/202240
09/202290
10/2022150

 

How can I achieve this ?

 

Thanks in advance,

 

Best Regards,

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    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.

4 Replies

  • TypimWuerfel's avatar
    TypimWuerfel
    New Member
    3 years ago

    Hi Anonymous,

     

    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 
     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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))

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you Anonymous , it worked perfectly. I really appreciate it