Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate the total average days

Hello community!

 

I am stumped. So I am trying to do a "conversion" rate based on a start date from one table and a start date from another table. These two tables do not have a direct relationship, however, they are both connected to a main table through an ID. The main table includes the slicer # and the main table ID #. I am using a DAX where I pull the minimum date value based on the start date of each table, then I am using a datediff between those two minimum values.

 

What I need is for the sum of those conversion days for all of the main table #s and then I will divide that number by the count of main table IDs. 

 

Here's an example. Instead of it showing -254 I need it to total eveything in that table in order for me to divide it by the count. Hopefully this wasn't too confusing, Thanks!

 

 

                    

 

  • Hi Anonymous 

    please try

    Conversation Date (days/ID) =
    AVERAGEX (
    VALUES ( 'Main Table'[Main Table ID] ),
    CALCULATE (
    DATEDIFF ( MIN ( Table1[StartDate] ), MIN ( Table2[StartDate] ), DAY )
    )
    )

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    Hi Anonymous 

    please try

    Conversation Date (days/ID) =
    AVERAGEX (
    VALUES ( 'Main Table'[Main Table ID] ),
    CALCULATE (
    DATEDIFF ( MIN ( Table1[StartDate] ), MIN ( Table2[StartDate] ), DAY )
    )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable

      Wow I was overthinking this, LOL! Works perfectly. Thank you!