Filtering rows with LY Measure

[Anonymous]

Hi all!

It feels like I am missing something very simply, but I just can't figure it out.

I have a simple last year measure for Revenue that works perfectly well all the time:

LY Revenue =

CALCULATE(

    [CY Revenue],

    SAMEPERIODLASTYEAR('Dropoff Date'[Date]))

Of which CY Revenue is the following:

CY Revenue =

    SUMX(RAReporting,

            [Total Bill]

            -[Total Bill Tax 3]

            -[Damage]

            -[Fines]

            -[Admin Fee])

Now, when I want to filter the LY Revenue for short term rows only, I have the following measure:

LY Revenue (Short Term Only) =

CALCULATE(

    [LY Revenue],

    FILTER(RAReporting,RAReporting[LT/ST In RAReport]="Short Term"))

I get blanks in my table which has external filters active for my date table:
- Year: 2019
- Month: July

Can anyone help me how to get the right measure for LY Revenue filtered for Short Term?

PS. When I use a filter on the page for Short Term, it does work perfectly, but in the measure it doesnt work.

Thanks a lot!

Regards,

Merel

[Anonymous · Accepted solution]

First of all, I don't know what's going on since I can't see the model. Second of all, YOU SHOULD NOT USE EXPANDED TABLES IN FILTERS. This is dangerous and slows down execution big time. Change the measures to:

LY Revenue =
	CALCULATE(
	    [CY Revenue],
	    SAMEPERIODLASTYEAR('Dropoff Date'[Date]) -- Is 'Dropoff Date' your Date table?
	)

CY Revenue =
    SUMX(
    	RAReporting,
        RAReporting[Total Bill]
        - RAReporting[Total Bill Tax 3]
        - RAReporting[Damage]
        - RAReporting[Fines]
        - RAReporting[Admin Fee]
     )
 
LY Revenue (Short Term Only) =
	CALCULATE(
	    [LY Revenue],
	    KEEPFILTERS( RAReporting[LT/ST In RAReport] = "Short Term" )
	)

Please stick to the rule: column names MUST BE preceded with the name of the table but measures MUST NOT.

Best

Darek

[Anonymous]

Hi Darek,

Using KEEPFILTERS() works perfectly as you suggested! Thank you so much!

'Dropoff Date' is indeed my date table.

I am not sure I understand what you mean with your two warnings of not using expanded tables in filters or using column names in measures, or where I have done either of these, but I will look up these topics online to prevent future mistakes. But thank you for the warning!

I am super happy with the solution!

Thanks

Merel