Showing only the last occurrence of a series on a bar chart, with indication of change from previous

[Anonymous]

We have a list of scores, per "area", on a particular audit date. Data is exported from the source system into Excel, and through some ETL, results in the following dataset:

Area	Score	Date Of Audit
AAA	90	01/01/2000
BBB	86	01/01/2000
CCC	92	01/01/2000
AAA	63	08/01/2000
BBB	85	08/01/2000
CCC	69	08/01/2000
AAA	43	15/01/2000
BBB	95	15/01/2000
CCC	36	15/01/2000

I need to be able to visualise the current value per area, along with the change from the previous value. In Excel, this is what we've been using:

In Excel, we'd manually update the series included in the chart, and have a calculated column of the included values less the previous values, that would give us the difference. The latest values, and the change, would then be included as series in the Excel chart.

I need to replicate this in PowerBI. 

Showing the latest values in a chart is easy enough via adding the Master table's fields to the visualization, and apply a visualisation-level filter, to allow the user to select the audit for which they want to see data:

On this chart, I want to include a second value (or "series" I'm guessing) that would show the change from the previous value. 

Basically, if I understand it correctly, I need to add a column to the Master table, that would calculate the previous value, for every entry in the table? If selecting the audit date 15/01/2000, this column's value should be populated by the score of the related area captured on 08/01/2000.

Since I have other visualisations on the page, like this one (i.e. a KPI showing number of areas exceeding a pre-set target score, along with the change over time) 

... I want to shy away from creating a new table with only this information in it.

How do I create the column to calculate the previous value for each entry? Or is there a different way that the report should be architected?

I've made some headway by including an Index like this:

Index = RANKX(Master, Master[Date], , ASC, Dense)

Which results in the following dataset:

The next step would be to grab the Score value, for the same Area, where the Index value is one less than the entry's Index value, but don't know where to from here.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Create a meausre.

Measure =
VAR _previous =
    CALCULATE (
        MAX ( Master[Date Of Audit] ),
        FILTER (
            ALL ( Master ),
            Master[Date Of Audit] < SELECTEDVALUE ( Master[Date Of Audit] )
                && Master[Area] = SELECTEDVALUE ( Master[Area] )
        )
    )
VAR _value =
    CALCULATE (
        MAX ( Master[Score] ),
        FILTER (
            ALL ( Master ),
            Master[Date Of Audit] = _previous
                && Master[Area] = SELECTEDVALUE ( Master[Area] )
        )
    )
VAR _result =
    MAX ( Master[Score] ) - _value
RETURN
    IF ( _previous = BLANK (), MAX ( Master[Score] ), _result )

If I have misunderstood your meaning, please provide more details with your desired output and more sample data.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

I've completed one solution, but it is a little convoluted:

Created a PreviousIndex column:

PreviousIndex = Master[Index] - 1

Create a PreviousDate column:

PreviousDate = 
var _date = Master[Date] 
var _area = Master[Area] 
var _prevIndex = Master[PreviousIndex] 
var _prevdate = calculate(max(Master[Date]), FILTER(Master, Master[Area]=_area && Master[Index] = _prevIndex)) 
return _prevdate

Created a PreviousValue column:

PreviousValue = 
var _area = Master[Area] 
var _prevdate = Master[PreviousDate]
var _prevValue = CALCULATE(Max(Master[Score]), FILTER(Master, Master[Area] = _area && Master[Date] = _prevdate)) 
return _prevValue

Created a Change from Previous Value column:

Change From Previous Value = Master1[Score] - Master1[PreviousValue]

Then added this as a series to my chart.

Two questions:

1. When creating the PreviousValue column, the Master table is filtered based on the area and previous date. This returns a table (albeit only with a single entry). To get it to a scalar value, I use MAX which, since there is only one value, will return it. Is there a better way of converting a single row table to a scalar?

2. Is there a cleaner way to do this?