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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.