Multiplication with Days Difference

[michael_knight]

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

[v-alq-msft · Accepted 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.

[michael_knight]

That's second one was right on the money. Thank you very much, Allan!

[v-alq-msft]

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]

Thank you so much @v-alq-msft , you've been a lifesaver

[amitchandak]

@michael_knight , I am not clear on expected output. Can you explain with example

[michael_knight]

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