Forum Discussion

SteveODea's avatar
SteveODea
Helper I
6 years ago
Solved

Calculating Averages from Data Across Different Tables

I have a data structure which is causing me some problems in the calculating of a couple of measures...

 

I have 2 fact tables:

  • Applications with a Submitted Date
  • Status Table with Offer & Acceptances Status with a status date stamp

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I have structured this data in PowerBI with a simple Dates table as follows:

 

 

 

 

 

 

 

 

 

I now have three measure I am trying to create off this data:

  1. Count of Applications Submitted over a rolling 5 day period, which I wish to evaluate on the Submitted Date
  2. Average time it takes for an offer to be made (i.e. difference between DateSubmitted & StatusDate, where status = Offer) over a rolling 5 day period, which I wish to evaluate on the StatusDate
  3. Average time it takes for an offer to be accepted (i.e. difference between StatusDate, where status = Offer and StatusDate, where Status = Accept) over a rolling 5 day period, which I also wish to evaluate on the StatusDate

I have been able to work out a mesaure for item 1 which was the easy part:

CountOfAcceptances5Days =
VAR MaxDate = LASTDATE(Dates[Date])
RETURN
CALCULATE(DISTINCTCOUNT('Applications'[ApplicationsRef]),
FILTER(ALLSELECTED(Dates), Dates[Date] > MaxDate - 5 && Dates[Date] <= MaxDate))
 
However I'm really struggling on 2 & 3.
 
I know that I have to use USERELATIONSHIP since I want to evaluate my answer on the StatusDate and my default relationship on the Date table is with the Applications table but I do not want to change this.
 
Also 2 requires a date difference on dates on two separate tables, whilst 3 requires a date difference on dates in the same column!!!
 
Please help!
 
Thanks in advance.
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try this measure:

    AverageDecisionTurnaround5Days = CALCULATE(AVERAGEX(FILTER(ALLSELECTED('Status'),'Status'[Status]="Offer" && 'Status'[StatusDate] >= MAX('Dates'[Date])-30 && 'Status'[StatusDate] < MAX(Dates[Date])),'Status'[Turnaround]))

    And it shows:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

     

2 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please try this measure:

    AverageDecisionTurnaround5Days = CALCULATE(AVERAGEX(FILTER(ALLSELECTED('Status'),'Status'[Status]="Offer" && 'Status'[StatusDate] >= MAX('Dates'[Date])-30 && 'Status'[StatusDate] < MAX(Dates[Date])),'Status'[Turnaround]))

    And it shows:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

     

  • SteveODea's avatar
    SteveODea
    Helper I
    6 years ago

    So I've figured out 2...

     

    I created a column in the Status Table as follows

     

    Turnaround = DATEDIFF('Status'[DateSubmitted],'Status'[StatusDate],DAY)
     
    And then I created a measure using this column...

     

    AverageDecisionTurnaround5Days =
    VAR MaxDate = LASTDATE(Dates[Date])
    RETURN
    CALCULATE(AVERAGEX('Status','Status'[Turnaround]),
    'Status'[Decision] = "Offer",
    Dates[Date]>MaxDate-5,
    Dates[Date]<=MaxDate,
    USERELATIONSHIP('Status'[StatusDate],Dates[Date])
    )
     
     
    If anyone can suggest a solution for number 3...