Change colour of line based on future dates

[Craig3TCE]

Good morning all,

I have a data feed from SQL Server of bank transactions. These are plotted on a line chart with the quick measure "Running total" which works just as I want.

The data feed also includes future transactions and projections from our acounts team. What I'm wanting to do, is represent the future transactions/projection as a different colour.

To do this, so far I've tried creating a Measure that takes todays date and if the transaction is greater, label it "Projection" else "Actual. This won't let me place the Measure in the legend of the chart.

Then I created a CASE statement in the SQL query. This allows me to place the new column in the legend, but the running total gets reset.

Putting the data into a table shows that the running total does indeed get reset for future dates which it doesn't before the case statement.

Does anyone have any ideas on how to fix this?

Thank you in advance,

Craig

[amitchandak]

@Craig3TCE , have you tried actual and project as legend. Without break of the line, it is not possible to have two different colors

[Craig3TCE]

This is what gives the result screenshotted above with the broken line and what's represented in the table where when adding the CASE statement, resets the running total calculation based on the "Quick Measure" which worked perfectly fine before.

[Craig3TCE · Accepted solution]

I've managed to solve this by taking the "Running Total" element back to the SQL Query by using an OVER clause. Now the CASE statement doesn't reset the running total and I have the output needed