topic IF statement with FILTER in DAX Commands and Tips

IF statement with FILTER

Aden — Thu, 17 Mar 2022 23:18:11 GMT

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

Re: IF statement with FILTER

OwenAuger — Fri, 18 Mar 2022 00:09:34 GMT

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

Re: IF statement with FILTER

Aden — Fri, 18 Mar 2022 00:28:20 GMT

Great thanks Owen. That done the trick!

Cheers