Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

Create DAX filter (Dynamic date filter based on parameter) for use in slicer

Dear all,

 

i need your help on creating the following:

 

I need to create a DAX formula, which filters a range of dates based on a date parameter in order to use this later to filter my slicer.

I have attached a workbook, so that it becomes more clear.

 

 

The situation as follows:

I have 3 entities, 2 currencies and 8 months of data.

PAR_DAT: This is a data parameter with only 3 dats available (31.10.2019, 30.11.2019 & 31.12.2019)

FORECAST: This is a KPI. It will provide me with forecast data for the next 3 months based on the parameter date chosen.

 

Problem Scenario

The entity PCA only has forecast data for January 2020 and February 2020 in AED currency. However, entity PCM has forecast data from January 2020 until February 2020 for AED currency.

Now if I choose PAR_DATE = 2019-11 and CURRENCY = AED, and check my ENTITY slicer, I can see PCM & PCA, which is correct.

However, if I choose PAR_DATE = 2019-12 and CURRENCY = AED, my ENTITY slicer contains PCA, which is wrong. Because PCA only has forecast data for Jan & Feb 2020.

The slicer is including all entity, regardless of what the parameter chosen in the PAR_DATE field.

 

I now need to create a filter / DAX formula, which can filter on visual level the CURRENCY slicer or even on the whole page.

 

How can i do this? How can i create a MEASURE and filter a date range based on PAR_DATE?

----------------------------------------------------------------------------

DATE_FILTER =

VAR CurrentDate = MAX(PAR_DATE)

VAR StartDate = EOMONTH(CurrentDate;2)

VAR EndDate = EOMONTH(StartDate;3)

VAR Result = DATESBETWEEN(DATE;StartDate;EndDate)

RETURN

Result

----------------------------------------------------------------------------

Logically like the code above. However, the code doesnt work. I need to put this filter on a page or visual level (slicer) if possible.

 

Thanks a lot in advanced

Denny

 

3 REPLIES 3
Anonymous
Not applicable

Can you please explain with an example.

Appreciate your Kudos.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

HelloAmitchandak,

 

thank you for the quick response. Could you please refer to the attached PBIX file as the example.

It is a very simple example created in the file.

Basically what i need is just to create a dax formula in oder to filter date range based on date parameter to be used in the slicer visual filter. The formula should no contain any measures. Just dates.

 

Thank you

 

Regards

Denny

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.