Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filtering rows with LY Measure

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...
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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