Forum Discussion
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
Community 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 )
)
)- AnonymousNot applicable
Wow I was overthinking this, LOL! Works perfectly. Thank you!