Dynamic Date Filter

[Cognosben]

Is it possible to create a dynamic date filter in a DAX expression where the result would be to have filter selections you can click for:

- Year to Date

- Last Month

- Last Week 

- Last Day

[v-shex-msft · Accepted solution]

HI @Cognosben,

I'd like to suggest you take a look at below link which has the similar requirement:

Calculating Averages for the same data for different time buckets (Daily/Weekly/Monthly)

Regards,

Xiaoxin Sheng

[Cognosben]

Hi @Anonymous

Thank you for reply. 

I have created the help table and the measure underneath but cannot get it to work. The measures are not showing when I put FilterMeasure into a graph. The individual measures are working as they should. What might be wrong?

FilterMeasure = IF(HASONEVALUE(Filter_Date[Filter]);SWITCH(VALUES(Filter_Date[Filter]);"YTD";[YTD_Number];"MTD";[YTM_Number];[YTD_Number]))

[Anonymous]

Hi @Cognosben

Does the FilterMeasure work outside of a graph? In a table for example or in Card visual?

In the graph, what dates do you have on the axis? Could you post a picture or link a file and i can have a look.

Best regards,
Gustav

[Anonymous]

Hi,

If I understand you correct you would want to do something like this.

One measure that changes depending on the datefilter you have filtered?

My suggestion is as follows:

Create a table (DatefilterTable) that has the following columns

SortKey DateFilter
1 YTD
2 Last Month
3 Last Week
4 Last Day

Sortkey is not needed but can be useful if you want a specific sortorder.

Create a measure for each YTD, Last Month, Last Week, Last Day filter.
Example [Measure YTD], [Measure Last Month]..

Finally you create:
FilterMeasure = IF(HASONEVALUE(DatefilterTable[DateFilter]),SWITCH(VALUES(DatefilterTable[DateFilter),"YTD",[Measure YTD],"Last Month",[Measure Last Month],"Last Week",[Measure Last Week],"Last Day",[Measure Last Day],[Your Default Measure])


[Your Default Measure] can be any of the above measures. It will display when you have no filter selected.

The DateFilter column can then be used in a slicer 🙂