Cumulative total by band within a date and revenue range

[Anonymous]

Hi,

I have a report with sales data and a set of revenue bands.  Each customer has a set of bands with a start and end date plus a min and max revenue range per band.  Each deal is unique to that customer (i.e. they have various numbers of bands with different start and end ranges per client).  

I'm trying to get a running total within each band, where it 'carries over' revenue spent to the next band.  I've used the DAX cumulative example from SQLBI and I've got what I need for running total per deal but it's not quite right for measuring their spend per revenue target. 

Sales Seg. Customers = 
VAR CustomersInSegment =                        -- Gets the customers in current segment
    FILTER (
        ALLSELECTED ( 'Dim.Deal'[Customer] ),
        VAR SalesOfCustomer = [Sales Amount] -- Computes Sales Amount for one customer
        VAR SegmentForCustomer =                -- Retrieves the segment
            FILTER (                            -- a customer belongs to
                'Dim.Deal',
                NOT ISBLANK ( SalesOfCustomer )
                    && 'Dim.Deal'[From] < SalesOfCustomer
                    && 'Dim.Deal'[To] >= SalesOfCustomer
            )
        VAR IsCustomerInSegments = NOT ISEMPTY ( SegmentForCustomer )
        RETURN IsCustomerInSegments
    )
VAR Result =
    CALCULATE (
        [Sales Amount],                        -- Expression to compute
        KEEPFILTERS ( CustomersInSegment )     -- Applies filter for segmented customers
    )
RETURN
    Result      

In my PBIX file I get this result:-

But what I also want is like this, so it takes the 300k above and runs is across the bands so we can see which band number/range they have met so far and I can use that for something like percentage achieved per band and so on.

From		To		Range Target		Achieved
0		150000		150000		150000
150001		275000		124999		124999
275001		400000		124999		24999
400001		10000000		9599999		0

I can't quite work out how to do the calculation to 'carry over' with the running total to the next row once they've reached the target on the row before and run that per customer deal.

Can anyone point me to the DAX that might help to achieve this please.  PBIX here .  Thank you.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please correct me if I wrongly understood your data model.

Please check  the below picture and the sample pbix file's link down below.

Sales total within range =
IF (
IF (
[Sales] >= MAX ( 'Dim.Deal'[To] ),
MAX ( 'Dim.Deal'[To] ),
[Sales] - MAX ( 'Dim.Deal'[From] )
) < 0,
0,
IF (
[Sales] >= MAX ( 'Dim.Deal'[To] ),
MAX ( 'Dim.Deal'[To] ),
[Sales] - MAX ( 'Dim.Deal'[From] )
)
)

https://www.dropbox.com/s/rz18wuq24h73b45/Values%20per%20band.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Jihwan_Kim]

Hi, @Anonymous 

Please check your link.

I think the link requests the password.

[Anonymous]

Sorry, should be viewable now.