cancel
Showing results for
Search instead for
Did you mean:
Member

## How to apply a dynamic filter to a measure?

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: How to apply a dynamic filter to a measure?

@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!

11 REPLIES 11
Super Contributor

## Re: How to apply a dynamic filter to a measure?

Just replace the code in the block with your own calc

```LY Measure = CALCULATE(
sum('Sales'[Revenue]),

PARALLELPERIOD('Date'[Date],-1,YEAR)

)```

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super Contributor

## Re: How to apply a dynamic filter to a measure?

@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!

Member

## Re: How to apply a dynamic filter to a measure?

@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?

Member

## Re: How to apply a dynamic filter to a measure?

@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?

Super Contributor

## Re: How to apply a dynamic filter to a measure?

@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!

Member

## Re: How to apply a dynamic filter to a measure?

@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

Super Contributor

## Re: How to apply a dynamic filter to a measure?

@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!

Member

## Re: How to apply a dynamic filter to a measure?

@Sean

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

Super Contributor

## Re: How to apply a dynamic filter to a measure?

@vitexo87

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

## Helpful resources

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 363 members 3,656 guests
Recent signins:
Please welcome our newest community members: