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 e...
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.

     

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