Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good afternoon everyone,
I'm struggling because I can not fin the solution for this problem:
This is my main table named 'DETALLES EMPLEADOS' (I have filtered it to ease the problem):
Okay so, as you can see there are three columns with dates but we are going to focus in the last two 'NUEVA FECHA CORREGIDA' and 'FIN ULT CUPO'.
I have also created a date table named 'CALENDAR'. This table has got only a column named 'DATE' whose first date is 01/01/2015 and the last one 31/12/2023 because I need to connect the data from my main table to this one to create a date filter in the slide.
The goal of my slide is to create a date filter like the attached below which could count the rows from the main table following the next rules:
Obviously the dates of the filter come from the date table that I've created and connected with the main table previously.
I have tried the following DAX but it's not throwing back the expected result:
Hope you could understand my problem and help me..
Kind regards,
Adrian
Solved! Go to Solution.
Hi again @d_gosbell !
I resolved the problem yesterday
I created two more date tables, one of them contain all the dates from 'NUEVA FECHA CORREGIDA' and the other one the dates from 'FIN ULT CUPO'. Then, I wrote this DAX formula:
By doing > min date or <= min date your condition on the 'DETALLES EMPLEADO'[NUEVA FECHA CORREGIDA] column will return true all the time, so basically what your expression is evaluating is the following:
Hi @d_gosbell !
Firstly, thank you for your answer!
Yes, I thought the same yesterday because the DAX isn't throwing back the employees whose contract start before the minimum date selected so.. should I maybe try to divide the DAX formula in two parts? I mean:
What do you think?
Regards,
Adrian
@Anonymous wrote:
Hi @d_gosbell !
so.. should I maybe try to divide the DAX formula in two parts? I mean:
- First part: [NUEVA FECHA CORREGIDA] >= MIN('CALENDARIO PRUEBA'[DATE]) && 'DETALLES EMPLEADO'[FIN ULT CUPO]<=MAX('Calendario Prueba'[Date].
- Second Part: [NUEVA FECHA CORREGIDA] < MIN('CALENDARIO PRUEBA'[DATE]) && 'DETALLES EMPLEADO'[FIN ULT CUPO]<=MAX('Calendario Prueba'[Date].
What do you think?
I think this will probably produce the same result as the existing measure.
Can you try expressing your question as a set of example data?
So if the slicer was set to a min date of 1 July 2019 and a max date of 31 August 2019 - Can you show tell us which of the example rows of data below you expect to be counted by adding a yes/no in the included column in the table below?
NUEVA FECHA CORREGIDA | FIN ULT CUPO | Included? |
15 Jul 2019 | 15 Aug 2019 | |
15 Jun 2019 | 15 Sep 2019 | |
10 Jun 2019 | 10 Jul 2019 | |
20 Jul 2019 | 20 Sep 2019 | |
23 May 2019 | 23 Jun 2019 | |
25 Sep 2019 | 25 Oct 2019 |
Hi again @d_gosbell !
I resolved the problem yesterday
I created two more date tables, one of them contain all the dates from 'NUEVA FECHA CORREGIDA' and the other one the dates from 'FIN ULT CUPO'. Then, I wrote this DAX formula:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |