Frequency of customer meetings

[PhilippeK]

Hi All,

I have a problem regarding a DAX formula in a measure. I guess my problem is classic.

I have 2 tables :

- a table 'Customers', including an 'Entry date' and an 'Ending Date' of each customer

- a table 'Meetings', the meetings realized with these customers

My goal : calculate the frequency of meetings [number of meetings]  / [number of days] for each customer, and calculate the global average (first part is ok but I can't find the global result)

Note: the days in contract are ok line by line, but the global calculation is wrong - so the frequency result is about 0 !

What did I do so far ?

1/ I created a Date table, linked to the meeting date + I finalized the model :

2/ I created 3 measures :

A. The number of meetings : 

M_Meetings_Count = COUNTROWS(Meetings)

B. the number of days in contract :

M_Days_In_Contract =

VAR start_Date = FIRSTDATE(ALLSELECTED(MyCalendar[MyDate]))

VAR end_Date = LASTDATE(ALLSELECTED(MyCalendar[MyDate]))

VAR current_Start = CALCULATE(max(Customers[Entry Date]),ALLEXCEPT(Customers,Customers[Cust ID]))

VAR current_End = IF(ISBLANK(CALCULATE(max(Customers[Ending Date]),ALLEXCEPT(Customers,Customers[Cust ID]))),end_Date,CALCULATE(max(Customers[Ending Date]),ALLEXCEPT(Customers,Customers[Cust ID])))

RETURN

IF(DATEDIFF(MAX(start_Date,current_Start),current_End,DAY)<0,0,DATEDIFF(MAX(start_Date,current_Start),current_End,DAY)+1)

C. The frequency :

M_FREQUENCY = DIVIDE([M_Days_In_Contract],[M_Meetings_Count],0)

-----------------------------

As you see in the screenshot above, there's an issue regarding the number of days in contract; this number of days looks correct line by line but the global result is wrong (61 days is of course a wrong result). That's the reason why the frequency calculation is wrong.

Here is the Pbix file : PBIX SOURCE 

Many thanks in advance for your time.

Philippe

[PhilippeK]

Many thanks HotChilli.

I realized another error from my part, not in the example but in the real project : I was working on the meeting table instead of the customer table, explaining why I failed when I tried the SUMX.

Anyway, all was not clear in my head and now it's much better.

Philippe

[HotChilli · Accepted solution]

I've had a go.  No guarantees but please test at your side.

I simplified the M_days_in_contract measure because I think you've got the correct context in the visual to make some of the variables simpler.  You can go with your own version or this as you please.

M_Days_In_Contract = 
VAR _slicer_start_Date = MIN(MyCalendar[MyDate])
VAR _slicer_end_Date = MAX(MyCalendar[MyDate])
VAR current_Start = MAX(Customers[Entry Date])
VAR _CustEndingDate = MAX(Customers[Ending Date])
VAR current_End = IF(ISBLANK(_CustEndingDate), _slicer_end_Date, _CustEndingDate)

RETURN
IF(DATEDIFF(MAX(_slicer_start_Date, current_Start), current_End,DAY) < 0,
0,
DATEDIFF(MAX(_slicer_start_Date, current_Start),current_End,DAY)+1)

// Number of days in contrat during the selected dates

and I used this in another measure 

M_Days_In_ContractChilli = SUMX(VALUES(Customers[Cust ID]), Customers[M_Days_In_Contract])

Let me know what you think.

Good luck