Forum Discussion

michael_knight's avatar
michael_knight
Post Prodigy
6 years ago
Solved

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

Need Help
Show and Tell
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

     

6 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
      michael_knight
      Post Prodigy
      6 years ago

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

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        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's avatar
      michael_knight
      Post Prodigy
      6 years ago

      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