Forum Discussion

Aden's avatar
Aden
Advocate I
4 years ago
Solved

IF statement with FILTER

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 

 

 
 
 
 
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    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

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    4 years ago

    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

  • Aden's avatar
    Aden
    Advocate I
    4 years ago

    Great thanks Owen. That done the trick!

    Cheers