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

[Anonymous]

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

[Anonymous]

PBIX File:

https://drive.google.com/open?id=1aQ_hJOL44I9K9tIErUFUo8m-WXvo6qHO

[amitchandak]

Can you please explain with an example.

Appreciate your Kudos.

[Anonymous]

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