Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

DATEDIFF I need to use to calculate averages

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. 

 

PBIDesktop_19eacjXN6g.png

 

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!
2 ACCEPTED SOLUTIONS
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can update your measure as below.

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

23.png

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

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"
    )
)

1.png

If you want this column to show depends on another filter, you can create another one measure.

 

3.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can update your measure as below.

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

23.png

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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"
    )
)

1.png

If you want this column to show depends on another filter, you can create another one measure.

 

3.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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)

 

PBIDesktop_vrpvFyhWe5.pngPBIDesktop_nlZaZfAsx3.png

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.