cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhilippeK
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)

Screen Shot 10-14-21 at 03.12 PM.PNG

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 :

Screen Shot 10-14-21 at 03.16 PM.PNG

 

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

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

View solution in original post

2 REPLIES 2
PhilippeK
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

 

 

 

HotChilli
Super User
Super User

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

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!