Search code examples
powerbidaxpowerbi-desktopmeasure

Create measure to calculate rows based on TO and FROM figures in another table

I have a report that fetches weekly data. I have a weeks table that contains the weeks and dates and relationships set up with the below tables.

I have a QueueSummary table; QueueSummary

I have ConciergeHours table;

ConciergeHours

The concierge hours differ from day to day, so i keep the start and finish time in a table so we can update it dynamically.

I am trying to create a measure to calculate how many interactions we have had, between the hours in the ConciergeHours table. Keep in mind this is a weekly report, so on any given day during the week, the hours may be different.

The calculation we are after is;

Number interactions accepted on queue LinkConciergeVQ (QueueSummary table) between the starthour and endhour in ConciergeHours table MINUS Number interactions offered on queue LinkVQ (QueueSummary table) between the starthour and endhour in ConciergeHours table

I currently have the measure looking like this. It works, but the between hours are not respected correctly, which i understand would be a result of the MIN, MAX.

m_deflections = 
CALCULATE (
    SUM ( QueueSummary[s_accepted] ),
    QueueSummary[queue] = "LinkConciergeVQ",
    filter(QueueSummary, [hour] >= min(ConciergeHours[starthour])),
    filter(QueueSummary, [hour] < max(ConciergeHours[endhour]))
)
    - CALCULATE (
        SUM ( QueueSummary[s_offered] ),
        QueueSummary[queue] = "LinkVQ",
        filter(QueueSummary, [hour] >= min(ConciergeHours[starthour])),
        filter(QueueSummary, [hour] < max(ConciergeHours[endhour]))
    )

I hope i've explained this correctly.

If anyone could offer any help!?

Thanks so much

Solution

  • Not sure I understand your table relationships, but if its 1:* from ConciergeHours to QueueSummary via date then I think you can use this measure, or at least it puts you on the right track, I hope:

    m_deflections = 

var s_accept = 
SUMX(
    'ConciergeHours',
    var startH = 'ConciergeHours'[startHour]
    var endH = 'ConciergeHours'[endHour]
    return
    CALCULATE(
        SUM('QueueSummary'[s_accepted]),
        'QueueSummary'[hour] >= startH && 'QueueSummary'[hour] < endH
    )
)

var s_offered = 
SUMX(
    'ConciergeHours',
    var startH = 'ConciergeHours'[startHour]
    var endH = 'ConciergeHours'[endHour]
    return
    CALCULATE(
        SUM('QueueSummary'[s_offered]),
        'QueueSummary'[hour] >= startH && 'QueueSummary'[hour] < endH
    )
)

return 
s_accepted - s_offered