IF statement with FILTER

[Aden]

When i use this 

Fees % of Budget to last month 2 =

CALCULATE(DIVIDE([Net Fees], SUM('Budgets'[billamt_perday]))*100,

FILTER('Calendar', MONTH([trandate])<MONTH(Today())-0))

I get the result i want ie only show data up to the end of last month.

However that dosnt work when looking at a previous year. 

So i add an IF to say if the current year is selected use Fees % of Budget to last month 2, if not then just use the unfiltered version.

Fees % of Budget to last month =

IF( format(YEAR(TODAY()),"yyyy") = [Selected Year Only] ,

[Fees % of Budget to last month 2], [Fees % of Budget])

Problem is by doing this the data displays the current month that i want filtered out. 

What am i doing wrong?

The output i want is 

[Aden]

Great thanks Owen. That done the trick!

Cheers

[OwenAuger · Accepted solution]

Hi @Aden 

In order to filter on months before the current month, I would suggest using EOMONTH to get the last date of the previous month, then filter on dates <= that date. This can be wrapped in KEEPFILTERS to intersect with the current filter context.

Here is how you could write the measure:

(Note: I'm assuming your 'Calendar' table is marked as a date table, and 'Calendar'[trandate] is the date column.)

Fees % of Budget to last month 2 =
CALCULATE (
    DIVIDE ( [Net Fees], SUM ( 'Budgets'[billamt_perday] ) ) * 100,
    KEEPFILTERS ( 'Calendar'[trandate] <= EOMONTH ( TODAY (), -1 ) )
)

Does this give the expected result?

Regards,

Owen