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 ...
  • 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

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