Help needed with a visual

[MikeC1]

Hi All, 

I'm a little stuck and was wondering if someone could help out. 

I have two datasets, let these be: 

1. Baselines

2. Impacts

I need to create a line chart that shows the baseline  and baseline +/-  impacts per month.

The 'Baselines' dataset is structured like so: 

Dept            Project            Source           2018 Baseline

IT                 Project1          Funder1         2,000,000

IT                 Project1          Funder2         20,000

IT                 Project2          Funder1         30,000

IT                 Project3          Funder1         32,000

IT                 Project3          Funder2         2,000

HR               Project4          Funder1         42,000

Where the 'baseline' line on the chart will be the sum of 2018 baseline for each dept or project (depending on the filters applied). The baseline will be the same for every month of the year (straight line).  Baseline for IT would be - 2,084,0000, baseline for Project 1 will be 2,020,000. Project would never be across two depts. 

The 'Impacts' dataset is structured like so:

Month         Dept             Project             Impact value

Feb               IT                 Project1           -100,000

Mar              IT                 Project1           +25,000

Feb              HR                Project1           -2,000

Jun               IT                 Project2           +5,000

Jul                IT                 Project1            -30,000

Where the 'baseline +/- impacts' line on the chart will be the project baseline (from 'Baselines' dataset) + cumulative value of impacts by period.

Quick illustration of what I'm after (excel chart) 

These need to be able to be affected by slicers - project and dept. 

I have tried using rankx but only got it to work in a custom table which doesn't include dept and project, just overall values. 

Hope that makes sense. 

Thanks. 

[v-jiascu-msft · Accepted solution]

Hi @MikeC1,

Please check out the demo in the attachment.

1. Create three more table. 

2. Establish proper relationship.

3. Create two measures.

Baseline = sum(Baselines[2018 Baseline])

Baseline+/-Impacts =
SUM ( Baselines[2018 Baseline] ) + SUM ( Impacts[Impact value] )

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @MikeC1,

Please check out the demo in the attachment.

1. Create three more table. 

2. Establish proper relationship.

3. Create two measures.

Baseline = sum(Baselines[2018 Baseline])

Baseline+/-Impacts =
SUM ( Baselines[2018 Baseline] ) + SUM ( Impacts[Impact value] )

Best Regards,

Dale

[MikeC1]

Thanks for your response - however it's not exactly what I'm after. Unfortunately, the 'baseline+/- impacts' can't go back to the original baseline figure. For example, after the -100,000 impact in Feb, baseline falls to 1,920,000 then in March, after the 25,000 increase it should go up to 1,945,000 instead of 2,045,000. 

Hope that makes sense. 

[v-jiascu-msft · Accepted solution]

How about this one?

Baseline+/-Impacts =
CALCULATE (
    SUM ( Baselines[2018 Baseline] ) + SUM ( Impacts[Impact value] ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Best Regards,

Dale

[MikeC1]

Sorry - I do have an additional question. 

After the impact in March, value goes up to 1,945,000 and then in April it goes back to baseline. Is there any way to 'stop' the line by nulling the value of the 'baseline +/- impact' if there were no impacts in that month? The line would have to join up with May if there happened to be an impact in that month. 

Thanks

[v-jiascu-msft]

Hi @MikeC1,

How to show it up? 0 or blank? Please try the formula below. The blue part could be 0.

Baseline+/-Impacts =
IF (
    SUM ( Impacts[Impact value] ) = 0,
    BLANK (),
    CALCULATE (
        SUM ( Baselines[2018 Baseline] ) + SUM ( Impacts[Impact value] ),
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        )
    )
)

Best Regards,

Dale

[MikeC1]

Thanks,

That does indeed cut the line but unfortunately it seems that if a month is skipped, the cumulative sum resets. In the below screenshot, there have been various impacts over 3 months, no impact in the 4th and an impact in the 5th. Rather than adding the impact value of the 5th month to the cumulative value of impacts by 3rd month, it adds it to the baseline again. 

Also the line doesn't join up:

[v-jiascu-msft]

Hi @MikeC1,

The line can't join the blanks. But it can join the 0s. Please try to replace the blank() with 0.

Best Regards,

Dale

[MikeC1]

To give a better example, it's the red line i'm after: 

[v-jiascu-msft]

Hi @MikeC1,

It seems my second post is the answer. Isn't it?

Baseline+/-Impacts =
CALCULATE (
    SUM ( Baselines[2018 Baseline] ) + SUM ( Impacts[Impact value] ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Best Regards,

Dale

[MikeC1]

Ah - sorry. In the example I used months as the x axis but my dataset actually contains periods instead, as below:

Because of this, my measure looks like this:

Baseline + Impact = CALCULATE(sum(baselines[2018 baseline]) + sum(impacts[impact value]),FILTER(ALL(Periods[Rank]), Periods[Rank] <= MAX(Periods[Rank])))

which makes the line look like this:

The table 'periods' and 'impacts' are joined by the 'concat' column.

[MikeC1]

I seem to have resolved it by adding the first date of the period to the 'periods' and 'impacts' tables, joining them by the date instead of concat. The measure seems to work then. 

[v-jiascu-msft]

That's great. 

Best Regards,

Dale

[MikeC1]

Thanks again Dale, really appreciate your help. 

In that case the line goes all the way to 0 before going back up, as per below picture:

And unfortunately the value in 5th month still adds the impact value to the original baseline instead of the last value of 'baseline + impact'. 

Is there a way to set that IF formula to bring back last month's 'baseline + impact' value if the value on impact in the month was 0? 

[MikeC1]

That does indeed seem to work! Thank you!