DATEDIFF I need to use to calculate averages

[Anonymous]

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: 

Average days to accept offer for one candidate = CALCULATE( AVERAGE([end_date]), FILTER(report, report[end_date] <> 0 ))

Can I have a simple DAX formula like the 1st one, to use to calculate average days of an accepted offer that ignores the 0's and displays the total average at the bottom? 

I don't want to fill my report with Conditional Columns, as it is memory consuming. DAX seems to be more optimized. 

Thank you very much!

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

We can update your measure as below.

Days till Offer accepted = 
DATEDIFF (
    AVERAGEA ( 'Projects'[start_date] ),
    AVERAGEA ( 'Projects'[end_date] ),
    DAY
)

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

We can update your measure as below.

Days till Offer accepted = 
DATEDIFF (
    AVERAGEA ( 'Projects'[start_date] ),
    AVERAGEA ( 'Projects'[end_date] ),
    DAY
)

[Anonymous]

Thank you very much @v-frfei-msft , works very well, can I add a Filter to this formula to ignore all the 0's ?

[v-frfei-msft · Accepted solution]

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.

[Anonymous]

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)