Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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]))
Solved! Go to Solution.
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!
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!
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?
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!
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
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!
I did not find this 2 buttons with down arrow, could you point at the image?
I'm not using month, just the same year, but now it worked, thanks for the help
Just replace the code in the block with your own calc
LY Measure = CALCULATE( sum('Sales'[Revenue]), PARALLELPERIOD('Date'[Date],-1,YEAR) )
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?
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |