Cumulative Total on Line Chart with Specific Start Date that Ignores Previous Transactions

[gogrizz]

I have a table of 280k rows of data (called "Product Timeline") showing all transactions of all companies with all of our products.  It shows the date of transaction, company_ID, product_ID, amount of the transaction, and the transaction_type.

What I'm trying to do is calculate the cummulative sum (i.e. running total) that tracks a specific company's actions with a specific product.  I have a measure that works so far:

CALCULATE(
    SUM( 'Product Timeline'[amount] ),
    'Product Timeline'[product_ID] = 1,
    FILTER(
        ALLSELECTED( 'Product Timeline' ),
        'Product Timeline'[date] <= MAX( 'Product Timeline'[date] )
    )
)

When I put it in a line chart with a standard date table on the x-axis, I put a slicer on the page so that only 1 company is selected at any time, and it works:

The problem that I have is that some companies have an "adjustment" that is indicated in the "transaction_type".  When a company has an adjustment, I have another measure that gives me the "adjustment_date".

Whenever a company has an adjustment_date,  I want the running tally (in the first measure) to START on the adjustment date and ignore all the running total values (the ups and downs) that occured previously.

EXAMPLE: in the chart above, if this company had an "adjustment" on January 1, 2018 and the adjustment amount was 5,000, the chart above would show the x-axis starting in 2018 (ignoring all the values pre-2018 values) and the line chart would start at 5k (instead of it's current 35k value) and running tally would continue as normal from that point.

Thanks for any help or suggestions!

[v-yanjiang-msft · Accepted solution]

Hi @gogrizz ,

According to your description, each company has a related adjustment date, you want to use the corresponding adjustment date in the measure.

I create a sample.

Here's my solution, create a table listing the adjustment date of each company. Then create relationship between the two tables with the company_ID column.

Then modify the formula like this:

Measure =
CALCULATE (
    SUM ( 'Product Timeline'[amount] ),
    'Product Timeline'[product_ID] = 1,
    FILTER (
        ALLSELECTED ( 'Product Timeline' ),
        'Product Timeline'[date] <= MAX ( 'Product Timeline'[date] )
            && 'Product Timeline'[date] >= RELATED ( 'Adjustment Table'[Adjustment Date] )
    )
)

Then the visual axis will change according to the company_ID slicer.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[gogrizz]

I'm getting close but still missing something.  If the adjustment date is January 1, 2018 I can what I want if I put that date/value directly in the measure:

CALCULATE(
    SUM( 'Product Timeline'[amount] ),
    'Product Timeline'[product_ID] = 1,
    FILTER(
        ALLSELECTED( 'Product Timeline' ),
        'Product Timeline'[date] <= MAX( 'Product Timeline'[date] ) &&
        'Product Timeline'[date] >= DATE( 2018, 1, 1 )
    )
)

However, the adjustment date is always changing depending on the company, so I currently have that value being calculated in a different measure.  If I substitute the "DATE( 2018, 1, 1 )" portion in the above formula using a different measure that has the exact same value...it falls apart.

I'm hoping someone can help me connect the final piece...