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

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors