New Measure is removing the filter

[ZMalik1]

Hi all, first time posting.

I have a filter running on my dataset which takes anything as current month and above as CY else PY and I've filtered to just CY.

I'm now calculating change in current week "CW" vs previous week "PW" but as that change is -100% because of both values being blank the filter has now been kicked out and it shows months I'm not interested in.

Any idea how I could correct please? If I filter asks change to not show -100% then I lose those values where there was a value in CW but not PW and vice versa.

Thank you for your help

[Alex87 · Accepted solution]

Hello @ZMalik1 ,

the variables will hold the sum of your numeric colums. Based on your screen show I would say that

VAR _CW = SUM('YourTableName[CW ASKs])

VAR _PW =  SUM('YourTableName[PW ASKs])

[ZMalik1]

Worked - brilliant. Thank you!

Am I right in thinking the VAR _CW is where you're denoting the variable and getting a short form of it which is what is calculated out later in the code. (Interesting to see that approach)

Thanks again!

[ZMalik1]

Ah right - I see, thank you!

Let me give that a try. Appreciate it. 🙂

[ZMalik1]

Hi Alex,
Thank you very much for that, I get what you mean and you're absolutely right.

Thanks for sharing the code, I seem to get a reference error -

So I tried to replace "cw" with the actual column name which is cw asks and the same for pw but seems to still throw it out and doesnt pick up the column, you can what results it finds instead and also showing the second from the end 3rd and 2nd column that are the reference points as guidance -

Thanks for your help!

[Alex87]

You need to put a condition to exclude the situation where both CW and PW are empty.
Give this a try:

Measure = 
VAR _CW = [CW]
VAR _PW = [PW]
VAR _Result = 
IF(
    ISBLANK(_CW) && ISBLANK(_PW), BLANK(), 
    DIVIDE((_CW -_PW), _PW )
)
RETURN
_Result

If it answers your need, please mark the post as the solution. Thanks!