5 Replies Latest reply on Jan 15, 2019 11:40 AM by Shaquelle Jacobs

# Filters

I created a 12Months this year calculation.  I Replace Today() with a parameter because i want the user to be able to select the date. By doing so i have two date filters. How can i get one date filter only ?

Mileage

if [Date Reported] >= DATEADD('month',-12,[Selected Date ])+1 then [Daily Miles]

END

if [Date Reported] >= DATEADD('month',-12,[Selected Date ])+1 then [Daily Rc Cnt]

END

MDBF

sum(if ([Date Reported]) >= DATEADD('month',-12, [Selected Date ])+1 then [12 Months Mileage This Year ] END)

/

sum( [12 Months RC This Year ])

• ###### 1. Re: Filters

Make one calc:

if [Date Reported] >= DATEADD('month',-12,[Selected Date ])+1 then 1 else 0

END

Put that on your filters shelf and select for value = 1.

All the measures will be filtered to those dates.

1 of 1 people found this helpful
• ###### 2. Re: Filters

Thank you very much Joe

• ###### 3. Re: Filters

what about previous 12 Months

Mileage

if

[Date Reported] >= DATEADD('month',-24, [Selected Date ])+1

and

[Date Reported] <= DATEADD('month',-12,[Selected Date ])

and

NOT ISNULL([Fixed Level Of Detail Expression for Mileage  ])

then [Daily Miles] END

if

[Date Reported] >= DATEADD('month',-24, [Selected Date ])+1

and

[Date Reported] <= DATEADD('month',-12,[Selected Date ])

and

NOT ISNULL([Fixed Level Of Detail Expression for RC ])

then [Daily Rc Cnt] END

mdbf

IFNULL(INT(sum(if [Date Reported] >= DATEADD('month',-24,[Selected Date ])+1 and [Date Reported] <= DATEADD('month',-12,[Selected Date ]) then [ 12 Months Previous Mileage ]

end)

/

sum( [ 12 Months Previous RC])),0)

• ###### 4. Re: Filters

I think I need to see a sample workbook to understand how 12 vs 24 come into play.

The point is that if you filter to the dates you want, all the measures get filtered along with that.  You don't need to mess with calcs to limit the data in the measures.

So maybe:

if

[Date Reported] >= DATEADD('month',-24, [Selected Date ])+1

and

[Date Reported] <= DATEADD('month',-12,[Selected Date ])

then 1 else 0 END

1 of 1 people found this helpful
• ###### 5. Re: Filters

i will put together a workbook and upload it to you in the morning.

Thanks