Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Exports,
As you see in photot attached, I am not referencing time and used time intelligence function and it give me a number, and npte in my data in data I have year only,
How I can deal with that? anf if its no give me zero, it can make % between 2015 % 2020 whwn I am not referncing time.
Solved! Go to Solution.
Hi @Anonymous
Maybe you can try this expression to do that:
yoy % =
VAR _amountly =
CALCULATE ( [value], DATEADD ( 'Table'[Date], -1, YEAR ) )
VAR _yoy = [value] - _amountly
VAR _result =
DIVIDE ( _yoy, ABS ( _amountly ) )
VAR _minyear =
CALCULATE (
[value],
FILTER (
ALLSELECTED ( 'Table'[Date] ),
[Date] >= DATE ( YEAR ( MIN ( 'Table'[Date] ) ), 1, 1 )
&& [Date] <= DATE ( YEAR ( MIN ( 'Table'[Date] ) ), 12, 31 )
)
)
VAR _maxyear =
CALCULATE (
[value],
FILTER (
ALLSELECTED ( 'Table'[Date] ),
[Date] >= DATE ( YEAR ( MAX ( 'Table'[Date] ) ), 1, 1 )
&& [Date] <= DATE ( YEAR ( MAX ( 'Table'[Date] ) ), 12, 31 )
)
)
RETURN
IF (
ISFILTERED ( 'Table'[Date].[Year] ),
_result + 0,
DIVIDE ( _maxyear - _minyear, ABS ( _minyear ) ) + 0
)
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Maybe you can try this expression to do that:
yoy % =
VAR _amountly =
CALCULATE ( [value], DATEADD ( 'Table'[Date], -1, YEAR ) )
VAR _yoy = [value] - _amountly
VAR _result =
DIVIDE ( _yoy, ABS ( _amountly ) )
VAR _minyear =
CALCULATE (
[value],
FILTER (
ALLSELECTED ( 'Table'[Date] ),
[Date] >= DATE ( YEAR ( MIN ( 'Table'[Date] ) ), 1, 1 )
&& [Date] <= DATE ( YEAR ( MIN ( 'Table'[Date] ) ), 12, 31 )
)
)
VAR _maxyear =
CALCULATE (
[value],
FILTER (
ALLSELECTED ( 'Table'[Date] ),
[Date] >= DATE ( YEAR ( MAX ( 'Table'[Date] ) ), 1, 1 )
&& [Date] <= DATE ( YEAR ( MAX ( 'Table'[Date] ) ), 12, 31 )
)
)
RETURN
IF (
ISFILTERED ( 'Table'[Date].[Year] ),
_result + 0,
DIVIDE ( _maxyear - _minyear, ABS ( _minyear ) ) + 0
)
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , based on what I got. Force the range again
example
0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))
User | Count |
---|---|
51 | |
36 | |
20 | |
14 | |
13 |
User | Count |
---|---|
94 | |
72 | |
30 | |
21 | |
13 |