cancel
Showing results for
Did you mean:
Highlighted
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

PBIX:

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

Cheers,

Mike

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Multiplication with Days Difference

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 =
CALENDAR(
),
"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 =
CALENDAR(
),
"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.

6 REPLIES 6
Highlighted
Super User IX

## Re: Multiplication with Days Difference

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

Proud to be a Super User!

Highlighted
Post Partisan

## Re: Multiplication with Days Difference

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

## Re: Multiplication with Days Difference

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 =
CALENDAR(
),
"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 =
CALENDAR(
),
"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.

Highlighted
Post Partisan

## Re: Multiplication with Days Difference

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

Highlighted
Community Support

## Re: Multiplication with Days Difference

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 =
CALENDAR(
),
"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

## Re: Multiplication with Days Difference

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

Announcements

#### Upcoming Events

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

#### Community Summit North America – Join Online!

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

#### Community Blog

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

Top Solution Authors
Top Kudoed Authors