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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi,

 

 

Have you tried something like this with IF / AND / DateDiff statements?

 

 Volume This Period New = IF(
AND(DATEDIFF(MAX(data[Estimate 1: En]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 1: St]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 2: En]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 2: St]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 3: En]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 3: St]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 4: En]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 4: St]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 5: En]), NOW(), DAY) >= 45 ,
AND(
DATEDIFF(MAX(data[Estimate 6: En]), NOW(), DAY) >= 45 ,

DATEDIFF(MAX(data[Estimate 6: St]), NOW(), DAY) >= 45)))))))))), 0, SUM(data[Volume This Period]))