Dynamic baseline

[Veles]

Hi all,

I'm trying to create a dynamic inflation measure where the user can select what month they want to use as the basline comparison from a slicer.

I currently have a calculated column that puts against each order line the average unit price paid in December 2016:

Dec Price =
IFERROR (
    CALCULATE (
        AVERAGE ( 'TS Data'[U Price] ),
        FILTER ( ALL ( 'TS Data'[Year/Period] ), 'TS Data'[Year/Period] = "201709" ),
        FILTER (
            'TS Data',
            'TS Data'[Code/Supplier] = EARLIER ( 'TS Data'[Code/Supplier] )
        )
    ),
    BLANK ()
)

I then have a measure that gives me a % difference between this calculated column and the actual price paid.

In order to make this baseline dynamic instead of stuck to Dec 16 I've created a separate table with no relationships to use so instead of filtering using:

'TS Data'[Year/Period]="201709"

It would be:

'TS Data'[Year/Period]=MAX(InflationBase[Year/Period])

However this wouldn't work as a calculated column so I would need to build the entire inflation calculation using a measure, I need row by row iteration on this formula so it looks like SUMX will be what I need but I'm having trouble getting it to work.

So far I have tried the below which I'm trying to get to calculate the average unit price x the order quantity line by line but it's just coming up with blank.

Inflation2 =
SUMX (
    'TS Data',
    CALCULATE (
        AVERAGE ( 'TS Data'[U Price] ),
        FILTER ( ALL ( 'TS Data'[Year/Period] ), 'TS Data'[Year/Period] = "201709" ),
        FILTER (
            'TS Data',
            'TS Data'[Code/Supplier] = EARLIER ( 'TS Data'[Code/Supplier] )
        )
    )
        * 'TS Data'[Ordered Quantity]
)

[v-ljerr-msft]

Hi @Veles,

Could try using the formula below to create the measure to see if it works in your scenario? 

Inflation2 =
VAR currentSelectedYearPeriod =
    MAX ( InflationBase[Year/Period] )
RETURN
    SUMX (
        'TS Data',
        CALCULATE (
            AVERAGE ( 'TS Data'[U Price] ),
            FILTER (
                ALL ( 'TS Data'[Year/Period] ),
                'TS Data'[Year/Period] = currentSelectedYearPeriod
                    && 'TS Data'[Code/Supplier] = EARLIER ( 'TS Data'[Code/Supplier] )
            )
                * 'TS Data'[Ordered Quantity]
        )
    )

Regards

[Veles]

Thanks @v-ljerr-msft

The formula gave me the below error:

However I moved one of the brackets at the end which stopped that. Still came up with blank though 

Inflation2 =
VAR currentSelectedYearPeriod =
    MAX ( InflationBase[Year/Period] )
RETURN
    SUMX (
        'TS Data',
        CALCULATE (
            AVERAGE ( 'TS Data'[U Price] ),
            FILTER (
                ALL ( 'TS Data'[Year/Period] ),
                'TS Data'[Year/Period] = currentSelectedYearPeriod
                    && 'TS Data'[Code/Supplier] = EARLIER ( 'TS Data'[Code/Supplier] )
            ))
                * 'TS Data'[Ordered Quantity]
        )

It looks like ADDCOLUMNS might help me here but I can't get it working but this also just returns blank 

Inflation2 =
SUMX (
    ADDCOLUMNS (
        'TS Data',
        "Base U Price", IFERROR (
            CALCULATE (
                AVERAGE ( 'TS Data'[U Price] ),
                FILTER ( ALL ( 'TS Data'[Year/Period] ), 'TS Data'[Year/Period] = "201709" ),
                FILTER (
                    'TS Data',
                    'TS Data'[Code/Supplier] = EARLIER ( 'TS Data'[Code/Supplier] )
                )
            ),
            BLANK ()
        )
    ),
    [Base U Price] * 'TS Data'[Ordered Quantity]
)

[Veles]

I've found that my ALL function isn't working as intended.

ALL('TS Data'[Year/Period])

I have slicers on the report for Period and Year (relationships below). So on my slicers I have selected July and 2017 (year/period 201804) and for those purchases, pull through what those products cost in Dec 2016 (year/period 201709). I found it I changed them to Dec 2016 it pulled through a number.

EDIT: If I select Dec 2017 on the filters it will just show the Dec 2017 price as the baseline. If I remove the filters it displays the December equivalent for all months.

It looks like it can't pull out the Dec 2017 price when the slicers are applied.

[v-ljerr-msft]

Hi @Veles,

Could you share a sample pbix file which can reproduce the issue, so that we can help further investigate on the issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards