cancel
Showing results for
Did you mean:
Regular Visitor

Frequency of customer meetings

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

Philippe

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Regular Visitor

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

Super User

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

Announcements

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.