Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I'm trying to create a column that multiplies the Day Difference column. The Day Difference Column shows the difference between the date a Lead came in and the Current Date.
We call each lead 7 times on Mon- Thurs & Saturday and 6 times on Friday & Sunday
Example,
Leads come in Tuesday, Days difference is 7
Wednesday - 7 Calls
Thursday - 7 Calls
Friday - 6 Calls
Saturday - 7 Calls
Sunday - 6 Calls
Monday - 7 Calls
Tuesday - 7 Calls
47 Calls Total
I originally thought I needed to create a measure that was as simple as IF Weekday = Mon-Thurs&Sat, *7. However, that doesn't take into account if the days difference has elapsed Friday and Sunday, like on the example above. So instead of 47 calls (Which is the accurate figure), I got a total of 49.
Does that make sense? If not, I'm more than happy to answer any questions!
Snippet of the data
PBIX:
https://www.dropbox.com/s/sh7za59gg8ubj84/Help%20-%20Calls.pbix?dl=0
Cheers,
Mike
Solved! Go to Solution.
Hi, @michael_knight
I wonder if the 'Day difference' includes the created date or the current date.
If it includes the created date, you may try the following calculated column.
Result =
var tab =
ADDCOLUMNS(
CALENDAR(
'Active Leads'[Created On],
'Active Leads'[Current Date]-1
),
"Value",
var _weekday = WEEKDAY([Date])
return
IF(
_weekday in {2,3,4,5,7},
7,
IF(
_weekday in {1,6},
6
)
)
)
return
SUMX(
tab,
[Value]
)
Result:
If it includes the current date, you may try the following calculated column.
Result =
var tab =
ADDCOLUMNS(
CALENDAR(
'Active Leads'[Created On]+1,
'Active Leads'[Current Date]
),
"Value",
var _weekday = WEEKDAY([Date])
return
IF(
_weekday in {2,3,4,5,7},
7,
IF(
_weekday in {1,6},
6
)
)
)
return
SUMX(
tab,
[Value]
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @michael_knight
I wonder if the 'Day difference' includes the created date or the current date.
If it includes the created date, you may try the following calculated column.
Result =
var tab =
ADDCOLUMNS(
CALENDAR(
'Active Leads'[Created On],
'Active Leads'[Current Date]-1
),
"Value",
var _weekday = WEEKDAY([Date])
return
IF(
_weekday in {2,3,4,5,7},
7,
IF(
_weekday in {1,6},
6
)
)
)
return
SUMX(
tab,
[Value]
)
Result:
If it includes the current date, you may try the following calculated column.
Result =
var tab =
ADDCOLUMNS(
CALENDAR(
'Active Leads'[Created On]+1,
'Active Leads'[Current Date]
),
"Value",
var _weekday = WEEKDAY([Date])
return
IF(
_weekday in {2,3,4,5,7},
7,
IF(
_weekday in {1,6},
6
)
)
)
return
SUMX(
tab,
[Value]
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That's second one was right on the money. Thank you very much, Allan!
Hi, @michael_knight
I'd like to suggest you create a table with a column which lists all holiday dates. Then you may try to modify the calculated column as below.
Result =
var tab =
ADDCOLUMNS(
CALENDAR(
'Active Leads'[Created On]+1,
'Active Leads'[Current Date]
),
"Value",
var _weekday = WEEKDAY([Date])
var _date = [Date]
return
IF(
_weekday in {2,3,4,5,7}&&NOT(_date in DISTINCT('Holiday'[Holiday Date])),
7,
IF(
_weekday in {1,6}&&NOT(_date in DISTINCT('Holiday'[Holiday Date])),
6
)
)
)
return
SUMX(
tab,
[Value]
)
Best Regards
Allan
@michael_knight , I am not clear on expected output. Can you explain with example
Hi @amitchandak
I want to multiply the DATEDIFF value from the Created On and Current Date.
I want to multiply it by this figures
Monday-Thursday & Sunday - 7
Friday & Sunday - 6
We get a Lead (Created On) on Saturday for example. The difference between Saturday and Thursday (Current Date) is 5 days
So using the figures above, this will be the multiplication = 6 (Sunday) + 7 (Monday)+ 7 (Tuesday)+ 7 (Wednesday) + 7 (Thursday)
That means the outcome I want is 33
Hopefully that's a bit clearer
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |