hiding empty columns in the matrix based on measures

[emillia1991]

Dear communty,

I'm new here and using Power BI since few months.

I wanted to present sales and sales difference in two different points in time. I want to choose points in time in slicer. My problem is that I have empty columns:

I created D_sales by applying measures, so that I can choose two years and it will calculate the percentage difference for these two years. Below the measures I used:

D_sales = 100*(DIVIDE(

    [Diff_sales],

    CALCULATE(

        SUM('Sheet1'[sales]),

        FILTER('Sheet1',Sheet1[year]=MIN(

            'Sheet1'[year]))

    )

))

Diff_sales = IF(

    HASONEVALUE('Sheet1'[year]),

    BLANK (),

    CALCUlATE(

        SUM(Sheet1[sales]),

        FILTER('Sheet1','Sheet1'[year]=MAX(Sheet1[year]

        ))

)

        - CALCULATE(

            SUM(Sheet1[sales]),

            FILTER('Sheet1','Sheet1'[year]=MIN(Sheet1[year]

            ))

        )

)

Hiding these empty columns by dragging is not possible because I want to update data and choose different years in the filter. I'm searching the Internet since yesterday. However, I'm not very succesful.  I was wondering if it is possible to write sth in the code so that it won't display empty columns.

[amitchandak]

@emillia1991 , Try like

Diff_sales =
var _max = maxx(allselected(Sheet) ,Sheet1[year])
var _min = Minx(allselected(Sheet) ,Sheet1[year])
return
IF(
HASONEVALUE('Sheet1'[year]),
BLANK (),
CALCUlATE(
SUM(Sheet1[sales]),
FILTER('Sheet1','Sheet1'[year]=_max
))
- CALCULATE(
SUM(Sheet1[sales]),
FILTER('Sheet1','Sheet1'[year]=_min
))
)

[emillia1991]

Thank you @amitchandak for your reply. Unfortunately it doesn't work. I still see the empty columns 😞