Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

% issue without referencing time

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.

 

 

Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello amitchandak , but in your code where is the year over year percentage?

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.