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

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]))

CEB

Frequent Visitor

8 years ago

martynbooth88,

Thanks for looking into this. I really appreciate it. I tried the formula that you posted, but made one change to not sum the volume in the end, so that it copies over the original volume per each row. I have never used the DateDiff function before. Unfortunalty I still can't get it to do what I need. I marked up an image if you have any further thoughts on it. Since today is 7/17/2018 and based on the dates in the estimate ranges, the yellow ones fall within the 45 day window starting from today. Those that don't have any dates that fall within the 45 day window should then be set to zero.

Again, I don't want to take too much of your time. If you can think of a way to make that happen, I would be very grateful.

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

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

CEB
Frequent Visitor
8 years ago
That worked great! Thank you both Anonymous and Vvelarde.

Forum Discussion

Dates between if statement challenge.

Related content

getting challenges with data science

No CACULATE challenge

OR Statement ?

Matrix Challenges

SQL Statement opcion error