Cumulative/Running Total

Hi ImkeF 

I'm not sure if I can upload a sample file but i'll try this... I was able to make this work in Excel example below

Example:

Table1-Active

Table2-Terms

Table3-Date

Hi jake223 ,

unfortunately you cannot upload files here. Instead, please upload your pbix to a cloud storage and paste link here.

The tables you've provided still bear too many loose ends for me (relationships, where does the turnover come from)

Also please make sure that your pbix already contains the measures that shall be references ( like [Turnover] )

Thanks.

jake223, 

Give this a try,

Cumulative = 
VAR minDate = CALCULATE(MIN('7_Date'[Date]) ; ALLSELECTED('7_Date'))
VAR maxDate = MAX('7_Date'[Date])
Return
SUMX(	
	SUMMARIZE(
	    FILTER(ALL('7_Date');
	        AND('7_Date'[Date] >= minDate ; '7_Date'[Date] <= maxDate)
	    );
        '7_Date'[Year Month];
        "Turnover_";
        [Turnover]
        );
    [Turnover_]
)

I gave it a try with an monthly index I had and atleast for me it seems to be producing the correct result. Let me know how it goes. 

Hi tex628 

This is what I get

Hello tex628  ImkeF 

I think I've stumbled on the issue but not sure how to correct. When I use the Date slider visual and set it to beginning of FY (June 1 2019) and End date (Dec 31 2019) the Cumulative total works.However, when I use the date dropdown visual it does not. It looks like when I use the date dropdown visual and select July the Date Min is 7/1 and Date Max is 7/31. See below both ways that I've tested this. 

1st image

2nd Image

My question is without using the date slider visual how do i get the Cumulative Measure to look back at past months from the current month that I select. The first image is displaying the correct information.

Hi jake223 

this depends on your measures and data model. 

If you've used "ALLSELECTED" somewhere in your measure-chain, this would be the expected behaviour:

Your slicers return different things: The first returns a range of periods and the secod one just one period. So you should expect different results.

I would recommend to start to create a mockup file that you can share here. This might help you in debugging the business logic you've used and narrow down the current culprits in your model.