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.
Hi all,
I am comparing thetwo Date columns in the below screenshot (this is not the real dataset of course so there are more dates dating back to years prior). I am comparing YoY averages of the time between candidates interview (Interview Date) to when they start (Start Date). I am sure you can do this in a calculated column and it works; However, I love using Zebra BI's Cards which show multiple measures in one view which compares the values & shows % and # increase/decrease.
Additionally, I need to add multiple filters in these measures.
-I have a separate Date table linked to "Start Date"
- Specific date range (last year December 2021)
-All columns values but certain ones excluded (for instance all "County" except for "San Fran" && "Apple"
I've also attached an example of what I am trying to solve with my calculation but it is not coming out correct & I have tried multiple versions of it including CALCULATE(FILTER)) etc.
Solved! Go to Solution.
Yes, you sure can 🙂
You can change the AVERAGEX expression as follows to check that both dates are nonblank (using Wait_Time Base as an example):
Wait_Time Base =
AVERAGEX (
'Table',
VAR StartDate = 'Table'[Start Date]
VAR InterviewDate = 'Table'[Interview Date]
RETURN
IF (
AND ( NOT ISBLANK ( StartDate ), NOT ISBLANK ( InterviewDate ) ),
INT ( StartDate - InterviewDate )
-- Otherwise return blank, which will be ignored by AVERAGEX
)
)
Regards,
Owen
A few of things to mention here:
Given those points, I would recommend writing two measures
Wait_Time Base =
AVERAGEX (
'Table',
INT ( 'Table'[Start Date] - 'Table'[Interview Date] )
)
Wait_Time =
CALCULATE (
[Wait_Time Base],
NOT 'Table'[County] IN { "Apple", "Lemon" },
DATESBETWEEN (
DimDate[Date],
DATE ( 2021, 12, 01 ),
DATE ( 2021, 12, 31 )
)
)
You could combine these into one measure also:
Wait_Time =
CALCULATE (
AVERAGEX (
'Table',
INT ( 'Table'[Start Date] - 'Table'[Interview Date] )
),
NOT 'Table'[County] IN { "Apple", "Lemon" },
DATESBETWEEN (
DimDate[Date],
DATE ( 2021, 12, 01 ),
DATE ( 2021, 12, 31 )
)
)
Regards,
Owen
Hi @OwenAuger & thank you for the response,
This almost worked. However, I did not notice for some of the values in "Interview Date" it is blank. Thus, I am receiving a number that is not accurate because it is still subtracting "Start Date" minus the blank "Interview Date" and giving me very high numbers as the difference. Any way to only include the calculation only for when the two are occupied with data?
Yes, you sure can 🙂
You can change the AVERAGEX expression as follows to check that both dates are nonblank (using Wait_Time Base as an example):
Wait_Time Base =
AVERAGEX (
'Table',
VAR StartDate = 'Table'[Start Date]
VAR InterviewDate = 'Table'[Interview Date]
RETURN
IF (
AND ( NOT ISBLANK ( StartDate ), NOT ISBLANK ( InterviewDate ) ),
INT ( StartDate - InterviewDate )
-- Otherwise return blank, which will be ignored by AVERAGEX
)
)
Regards,
Owen
Thanks Owen. This seemed to work. Appreciate the help.
Hi,
AVERAGEX needs table reference as its firs input so isntead of Table[column] use Table. For the CALCULATE use MAX(Table[Country]) instead of Table[Country].
Proud to be a Super User!
Hi ValterriN,
What does the MAX do in this case and what does it change?
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 |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |