Forum Discussion

CEB's avatar
CEB
Frequent Visitor
8 years ago
Solved

Dates between if statement challenge.

I have been stuck on this challenge for quite a while.   I am trying to adjust volume this period to zero in a new column, if none of the dates within 6 different estimate ranges, fall within a 45 ...
Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    CEB

     

    Try withs calculated column:

     

    Column =
IF (
    DATEDIFF ( TODAY (), Table1[Estimate 1: St], DAY ) > 0
        && DATEDIFF ( TODAY (), Table1[Estimate 1: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 1: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 1: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 2: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 2: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 2: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 2: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 3: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 3: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 3: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 3: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 4: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 4: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 4: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 4: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 5: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 5: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 5: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 5: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 6: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 6: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 6: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 6: En], DAY ) <= 45,
    Table1[Volume This Period],
    0
)

    Regards

     

    Victor

CEB's avatar
CEB
Frequent Visitor
8 years ago

If I use the DateDiff function on each estimate start and stop date from today's date, I get a positive or negative number. 

 

Estimate 2: En# = DATEDIFF(NOW(), data[Estimate 2: En], DAY)

 

Anything greater than zero and less than 45 is what I am looking for. Now I think I need to figure out how to look at all 12 date options and if just one of them is greater than zero and less than 45, use the original volume value, otherwise set to zero. 

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
8 years ago

CEB

 

Try withs calculated column:

 

Column =
IF (
    DATEDIFF ( TODAY (), Table1[Estimate 1: St], DAY ) > 0
        && DATEDIFF ( TODAY (), Table1[Estimate 1: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 1: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 1: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 2: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 2: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 2: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 2: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 3: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 3: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 3: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 3: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 4: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 4: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 4: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 4: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 5: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 5: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 5: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 5: En], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 6: St], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 6: St], DAY ) <= 45
        || DATEDIFF ( TODAY (), Table1[Estimate 6: En], DAY ) > 0
            && DATEDIFF ( TODAY (), Table1[Estimate 6: En], DAY ) <= 45,
    Table1[Volume This Period],
    0
)

Regards

 

Victor

  • CEB's avatar
    CEB
    Frequent Visitor
    8 years ago

    That worked great! Thank you both Anonymous and Vvelarde.