Empty values in matrix

[Br1-981]

Hi,

I have a data model like this:

I need to build a matrix:

Using YEAR from Calendar

Media from DIS_Media

and SPEND ACTUAL FROM Weekly Data 

it is working properly.

If I add a Calculated Measurement to show for each row/Year the difference against the previous year:

ActualSpend_EUR_PY = IF(Weekly_Calc[ActualSpend_EUR]>0,
    CALCULATE (
    Weekly_Calc[ActualSpend_EUR],
    OFFSET (
        -1,
        ALL ( 'Calendar'[Year] ),
        ORDERBY ( 'Calendar'[Year], ASC )
    )
),0)

then:

ActualSpend_EUR_VarPY = IF([ActualSpend_EUR_PY]>0,(DIVIDE(Weekly_Calc[ActualSpend_EUR], Weekly_Calc[ActualSpend_EUR_PY])-1),0)

The matrix is reporting all the years from the table Calendar:

While I would show just years where SPEND ACTUAL is more than 0, I tried using filters applied to the specific widgets.

Any Idea?

[Anonymous · Accepted solution]

Hi @Br1-981 

Try the following dax 

ActualSpend_EUR_PY = 
CALCULATE(
    [ActualSpend_EUR], 
    DATEADD('Calendar'[Date], -1, YEAR)
)

ActualSpend_EUR_VarPY = 
IF(
    [ActualSpend_EUR_PY] > 0,
    ([ActualSpend_EUR] - [ActualSpend_EUR_PY]) / [ActualSpend_EUR_PY],
    BLANK()
)

Finally, to ensure that the matrix only shows years where "SPEND ACTUAL" is more than 0, you can use a visual level filter on your matrix. Here's how to apply the filter:

1. Click on your matrix visual to select it.
2. Go to the "Filters" pane on the right side of the Power BI Desktop interface.
3. Under the "Visual level filters" section, find the "SPEND ACTUAL" measure.
4. Set the filter to show only values greater than 0.

If I misunderstood what you meant. please provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

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

[Br1-981]

Thanks a lot for your help, now the measurement part is working well.

The only problem is with the last step because It is showing values also for 2025 (where I have no actual spend) just because It is calculating the PY one, even filtering at visual level Spend>0 i got:

So I modified a bit the PY calculation:

ActualSpend_EUR_PY= IF([ActualSpend_EUR]>0,CALCULATE(
    [ActualSpend_EUR], 
    DATEADD('Calendar'[Date], -1, YEAR)
),BLANK())

and now it is working 😄 (no filtering at visual level needed):