cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cognosben Regular Visitor
Regular Visitor

Dynamic Date Filter

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

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamic Date Filter

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

4 REPLIES 4
gurrato Regular Visitor
Regular Visitor

Re: Dynamic Date Filter

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 🙂
Cognosben Regular Visitor
Regular Visitor

Re: Dynamic Date Filter

Hi @gurrato

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]))

 

 

Community Support Team
Community Support Team

Re: Dynamic Date Filter

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

Highlighted
gurrato Regular Visitor
Regular Visitor

Re: Dynamic Date Filter

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors