Calculate mesure if value not blank

[mairiau]

Hi Guys,

I am on a report and I would like to calculate the evolution between two figures (These figures are from Measures).

I want the calculation "Evolution 2018/2017 = ([Measure Latest RP 2018]-[Measure lastest RP 2017]) "only to operate if the values "Last Assortment 2017" and "Last Assortment 2018" are not blank. Otherwise, I don't want to see the evolution of the assortment, as I don't have enough elements to calculate.

Thanks in advance.

Mathieu

[v-frfei-msft]

Hi @mairiau ,

Try this one please.

=
IF (
    [Last Assortment 2017] = BLANK ()
        || [Last Assortment 2018 ] = BLANK (),
    - [Last Assortment 2018]-[Last Assortment 2017],
    BLANK ()
)

[Anonymous]

Try this and let me know

 = IF( Last Assortment 2017 <> 0, IF( Last Assortment 2018 <> 0, ([Measure Latest RP 2018]-[Measure lastest RP 2017], 0), 0)

[mairiau]

Hello,

Nice, we just have a problem with the formula, Power Bi does not seem to make the difference between zero and blank (cf picture). Do we need to change the formula of assortment last visit ?

Thanks in advance.

[v-frfei-msft · Accepted solution]

Hi @mairiau ,

Please try to use this one.

=
VAR a = [Last Assortment 2017] + 0
VAR b = [Last Assortment 2018 ] + 0
RETURN
    IF (
        a <> 0
            && b <> 0,
        [Last Assortment 2018] - [Last Assortment 2017],
        BLANK ()
    )

[Cmcmahan]

I would be slightly more explicit, because an actual data value of zero might be possible.

Evolution 2018/2017 = CALCULATE( ([Measure Latest RP 2018]-[Measure lastest RP 2017]), 
                                  FILTER('Table1',
                                         'Table1'[Last assortment 2017] <> BLANK() && 
                                         'Table1'[Last assortment 2018] <> BLANK() 
                                        )
                                )