Forum Discussion

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

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

Vvelarde

Community 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