Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello everyone,
I have used this solution to calculate the days between 2 dates: https://community.powerbi.com/t5/Desktop/Measure-to-Calculate-the-number-of-days-between-two-date-co...
It seems to work quite well with +-1 day sometimes, I believe it's because the columns are date/time and it subtracts the hours as well for some of the calculations.
The only problem is that at the bottom of the table appears "1" and not the average.
I have 3 column trials.
1. Days till Offer accepted -> The DAX formula calculated from the link above:
SWITCH ( TRUE (), MIN('Projects'[start_date]) < MIN('Projects'[end_date]), DATEDIFF ( MIN('Projects'[start_date]), MIN('Projects'[end_date]), DAY ), MIN('Projects'[start_date]) > MIN('Projects'[end_date]), DATEDIFF ( MIN('Projects'[end_date]), MIN('Projects'[start_date]), DAY )* -1 )
2. Average of days accepted offer -> A conditional column in power query with this M formula:
Duration.Days(Duration.From([#"end_date"]-[start_date]))
3. Average days to accept offer for one candidate -> An average DAX calculation of the conditional column above that ignores the 0's:
Solved! Go to Solution.
Hi @Anonymous ,
We can update your measure as below.
Days till Offer accepted = DATEDIFF ( AVERAGEA ( 'Projects'[start_date] ), AVERAGEA ( 'Projects'[end_date] ), DAY )
Hi @Anonymous ,
To update the measure as below.
Days till Offer accepted v2 = IF ( ISINSCOPE ( Projects[Name] ), FORMAT ( DATEDIFF ( AVERAGE ( 'Projects'[start_date] ), AVERAGE ( 'Projects'[end_date] ), DAY ), "General Number" ), FORMAT ( DIVIDE ( SUMX ( ALL ( Projects ), DATEDIFF ( AVERAGE ( 'Projects'[start_date] ), AVERAGE ( 'Projects'[end_date] ), DAY ) ), COUNTROWS ( FILTER ( ALL ( Projects ), Projects[start_date] <> Projects[end_date] ) ) ), "General Number" ) )
If you want this column to show depends on another filter, you can create another one measure.
Hi @Anonymous ,
We can update your measure as below.
Days till Offer accepted = DATEDIFF ( AVERAGEA ( 'Projects'[start_date] ), AVERAGEA ( 'Projects'[end_date] ), DAY )
Thank you very much @v-frfei-msft , works very well, can I add a Filter to this formula to ignore all the 0's ?
Hi @Anonymous ,
To update the measure as below.
Days till Offer accepted v2 = IF ( ISINSCOPE ( Projects[Name] ), FORMAT ( DATEDIFF ( AVERAGE ( 'Projects'[start_date] ), AVERAGE ( 'Projects'[end_date] ), DAY ), "General Number" ), FORMAT ( DIVIDE ( SUMX ( ALL ( Projects ), DATEDIFF ( AVERAGE ( 'Projects'[start_date] ), AVERAGE ( 'Projects'[end_date] ), DAY ) ), COUNTROWS ( FILTER ( ALL ( Projects ), Projects[start_date] <> Projects[end_date] ) ) ), "General Number" ) )
If you want this column to show depends on another filter, you can create another one measure.
Thank you very much @v-frfei-msft ! Quite a long formula, but it works! 😄
Instead of Projects[Name] I used the Candidate ID, as it is unique.
Instead of General Number I used "0"
Can you please confirm is that's what I was supposed to do?
I would love to understand the formula if I use it next time 😄 Thank you very much! You are awesome!
(edited) One other strange thing I've noticed is that the results are not recognized as whole numbers. When I want to sort them descending, they show me the 92, 91 first although the biggest numbers are 168, 146. Is there a solution to this? (Check screenshots below)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |