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 =
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'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

2 Replies

  • 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