Create a DAX Measure to act as a YTD filter

[nh27]

I have a 8-10 page report with about 3-4 visuals on each page which show a YTD picture, however the way the model has been designed is I have a Period lookup table with months 1-12 as values to reflect our Fiscal Year.

Every month rather than having to go into each visual and add a check box on period 1,2,3,4, etc I'm wondering if there is a way to create a measure where I include the selected values to apply to that filter?

[Syk]

You can do this with a relative date filter. However, you will need a date field which you could add to your period_lookup table or create a separate date table that you connect to the period_lookup table.

I should also note that your current filter does not get year to date data... Your period ID starts with April which means your filter is getting months April - December. Unless you're using a specific fiscal year, you will need to edit this as well.

[nh27]

Thanks, you are right the Fiscal year starts in Apr hence the period ID starting there.

I have created a conditional column that adds the last day of the month against each period, I presume I just now need to create a DAX measure to select a date range?

[Syk]

No, you need to add the date field to your filters and select the relative date option. You can specify 'in this year' or 'in the last x month'

[nh27]

This defeats the purpose of what I am trying to achieve if that is the case because I can just use the Period as a filter (which I am doing now).

I wanted to know if there is a DAX measure that selects values from a column within the measure itself so that I can apply the DAX measure as a filter as opposed to having to manually change the period range in each visual each month.

[Syk · Accepted solution]

Using the relative date filter will automatically adjust your filter. When a new month comes, you won't need to go and select 10, it will automatically adjust with the new month.

[nh27]

Thank you, it's done the trick. I've set relative date for the last 12 months, this will restrict my period column against the dates to work for the Fiscal Year Apr-March