Quick measure returns column with zeros

[Hsnoek]

I'm using a quick measure "difference from filtered value' to show differences between actual current year, budget and prior year.

However it now also compares actual current year vs actual current year and shows a variance column with zeros.

How can I hide this column with zeros in it? 

This is the related DAX formula:

m€ VAR =

VAR __BASELINE_VALUE =

    CALCULATE(

        SUM('Financemj'[m€]),

        'Financemj'[Budget_Oms_Year] IN { "Realisatie-2020" },

        ALL('Financemj'[Sort_Budget_type])

    )

VAR __MEASURE_VALUE = SUM('Financemj'[m€])

RETURN

    -IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE)

[v-robertq-msft · Accepted solution]

Hi, @Hsnoek 

According to your description, you want to hide the value of the column when it’s 0, you can try this DAX :

m€ VAR =
VAR __BASELINE_VALUE =
    CALCULATE(
        SUM('Financemj'[m€]),
        'Financemj'[Budget_Oms_Year] IN { "Realisatie-2020" },
        ALL('Financemj'[Sort_Budget_type])
    )
VAR __MEASURE_VALUE = SUM('Financemj'[m€])
Var _value=
IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE)
Return
If(_value=0,blank(),_value)

If this measure can not achieve your goal, you can post some sample data or sample pbix file(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Hsnoek , Can you create a measure using time intelligence and date table

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))


//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.