cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
Post Partisan

Multiplication with Days Difference

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

fgsdetrhgerg.PNG

PBIX:

https://www.dropbox.com/s/sh7za59gg8ubj84/Help%20-%20Calls.pbix?dl=0

 

Cheers,

Mike

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Multiplication with Days Difference

Hi, @michaelknight94 

 

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:

c1.png

 

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:

c2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

6 REPLIES 6
Highlighted
Super User IX
Super User IX

Re: Multiplication with Days Difference

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Post Partisan
Post Partisan

Re: Multiplication with Days Difference

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

Highlighted
Community Support
Community Support

Re: Multiplication with Days Difference

Hi, @michaelknight94 

 

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:

c1.png

 

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:

c2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Highlighted
Post Partisan
Post Partisan

Re: Multiplication with Days Difference

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

Highlighted
Community Support
Community Support

Re: Multiplication with Days Difference

Hi, @michaelknight94 

 

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

 

 

Highlighted
Post Partisan
Post Partisan

Re: Multiplication with Days Difference

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

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors