cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vitexo87 Member
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
Sean Super Contributor
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! Smiley Happy

11 REPLIES 11
Phil_Seamark Super Contributor
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!

Sean Super Contributor
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! Smiley Happy

 

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! Smiley Happy

vitexo87 Member
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

:Untitled2.png

 

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?

vitexo87 Member
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:

Untitled.png

 

What can I do in this case?

Sean Super Contributor
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! Smiley Happy

vitexo87 Member
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

 

Untitled3.png

Sean Super Contributor
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! Smiley Happy

vitexo87 Member
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?

Sean Super Contributor
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)

 

Chart-ExpandAll.png

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 108 members 1,703 guests
Please welcome our newest community members: