Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
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
User | Count |
---|---|
84 | |
81 | |
64 | |
53 | |
45 |
User | Count |
---|---|
100 | |
48 | |
41 | |
39 | |
38 |