topic Re: Filter within a measure in DAX Commands and Tips

Filter within a measure

Xaarie — Thu, 18 Jun 2020 10:13:28 GMT

I am trying to filter a denominator in a measure for the last 12 months.

My measure so far is Divide([WIP + debtors], [YTD fees}).

I need the YTD fees to be a rolling YTD. I have got the amount i require in another measure by applying a filter on a column of

Days from today = DATEDIFF(vwBillTotals[Date],TODAY(),DAY) where less than 365

on this measure

YTD fees = sum(vwBillTotals[BilledExVat])

But I don't know how to put this filter in the measure expression so that the orginal measure doesnt include all the data as the denominator and not just the last 365 days which is what i want.

Sorry if this is an easy question I am very new to this.

thanks

Re: Filter within a measure

Mariusz — Thu, 18 Jun 2020 10:29:03 GMT

Hi Xaarie

Try DATESINPERIOD
https://docs.microsoft.com/en-us/dax/datesinperiod-function-dax

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Filter within a measure

Xaarie — Thu, 18 Jun 2020 11:01:15 GMT

Thanks.

I have tried that in both direct query and import mode and it gives a 0 value. formula is:

DIVIDE([WIP + debtors], (CALCULATE(SUM(vwBillTotals[BilledExVat]),DATESINPERIOD(vwBillTotals[Date],TODAY(),-365,day))))

Re: Filter within a measure

Xaarie — Fri, 19 Jun 2020 15:07:09 GMT

Sorted thanks for the info