Forum Discussion

Veles's avatar
Veles
Advocate V
9 years ago

Dynamic baseline

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]
)

 

Need Help

4 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Veles,

     

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

    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's avatar
      Veles
      Advocate V
      9 years ago

      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 :smileysad:

       

      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 :smileysad:

       

      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's avatar
        Veles
        Advocate V
        9 years ago

        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.