How to apply a dynamic filter to a measure?

[vitexo87]

I need to create a measure that will always make the calculation referring to a year before the filter of the year applied, for example, the user filters the year of 2016 the measure has to make the calculation of the value of the measure of the year of 2015.

How can I do this? it is possible?

The measure is as follows: 

Meta Liquidação = (((sum(Fatos[Vl_Liquidado]) - sum(Fatos[Vl_Devolvido])+ sum(Fatos[Vl_DevolucaoCancelada]) - sum(Fatos[Vl_LiquidacaoCancelada]))*10)/100) + (sum(Fatos[Vl_Liquidado]) - sum(Fatos[Vl_Devolvido])+ sum(Fatos[Vl_DevolucaoCancelada]) - sum(Fatos[Vl_LiquidacaoCancelada]))

[Sean]

@vitexo87

Give this a try...  (SUMX goes thru every row so there's no need to include the SUM in case you are wondering)

Meta Liquidação =
SUMX (
    FILTER ( Fatos; Fatos[Year] = MAX ( Fatos[Year] ) - 1 );
    Fatos[Vl_Liquidado] - Fatos[Vl_Devolvido]
        + Fatos[Vl_DevolucaoCancelada]
        - Fatos[Vl_LiquidacaoCancelada]
)
    * 1,1

Let me know!

EDIT: Also multiplying => Value * 1.1 has the same effect as adding 10% to the Value => Value + (Value*(10/100)) = Value * 1.1

Hope this helps!

[vitexo87]

@Sean

I made the change you suggested but when I apply the year filter the value is blank, an image with the error follows:

What can I do in this case?

[Sean · Accepted solution]

@vitexo87

How about this...

Meta Liquidação =
SUMX (
    FILTER ( ALL ( Fatos ); Fatos[Ano_ID] = MAX ( Fatos[Ano_ID] ) - 1 );
    Fatos[Vl_Liquidado] - Fatos[Vl_Devolvido]
        + Fatos[Vl_DevolucaoCancelada]
        - Fatos[Vl_LiquidacaoCancelada]
)
    * 1,1

This should work!

[vitexo87]

@Sean

I applied the change to the extent and the problem of the blank value has been corrected, but the year value in the chart axis appears without the value of 2000, no matter the year I apply the filter

[Sean]

@vitexo87

This happens when you are using a Date Hieararchy in the Axis.

Go to the top left corner in the chart - there are 2 down arrow button - click Expand All

This should take care of it!

[vitexo87]

@Sean

I did not find this 2 buttons with down arrow, could you point at the image?

[Sean]

@vitexo87

What field are you using for the Axis? (if not a Hierarchy the button won't be there)

[vitexo87]

@Sean

I'm not using month, just the same year, but now it worked, thanks for the help

[Sean]

@vitexo87

Also try switching the X-Axis to Categorical?

[Phil_Seamark]

Just replace the code in the block with your own calc

LY Measure = CALCULATE(
			sum('Sales'[Revenue]),
						
			PARALLELPERIOD('Date'[Date],-1,YEAR)
						
			)

[vitexo87]

@Phil_Seamark

I tried to apply your suggestion, but my year size is not of the date type, but rather the whole numeor poi the information already comes from the database in this way, for example, 2017, 2016, 2015, 2014 ..... I have tried Convert the type of this information to date but the power bi has accused an error without log, it follows an image of how it was and the error that occurs when I try to apply its solution

:

And how was the expression of my measure:

CALCULATE (sum (Facts [Vl_Liquidate]) - sum (Facts [Vl_Devolved]) + sum (Facts [Vl_LiquidationCancelada])) 10) / 100) + sum (Facts [Vl_Liquid ]) - sum (Facts [Vl_Devolved]) + sum (Facts [Vl_DevolutionCancelada]) - sum (Facts [Vl_LiquidationCancelada])) PARALLELPERIOD (DMEGF_Ano [Year]; - 1; YEAR))

What do you suggest I can do in this case?