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 th...
Need Help
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.

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

    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. :smileyhappy:

     

    Regards