Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

IF DATEDIFF GREATER or LESS Than TargetDate

Hi Team

 

I have question, for datediff DAX.

Pls check my table below

 

Refer on "LOGIC" and "RESULT" , i want to know DAX code for it

 

Thanks a lot

Syaiful

  • This calculate column will get you started, but your logic isn't complete.

     

     

    Calculated Column = 
    IF(
        [Date Act] > [Date Referred],
        IF(
            [Date Act] > [Date Referred]+3,
            "Hit",
            "Miss"
        ),
        IF(
            [Date Act] < [Date Referred] - 3,
            "Hit",
            "Miss"
        )
    )

     

     You didn't explain why the bottom two comparisons should have a different calculation than the top two, and your third one is wrong. Date.Act is Feb 8. If Date.Act (feb 8 )is less than Date.Referred (Feb 8 )-3 (now Feb 5) then hit else miss. That is a MISS because Feb 8 is not less than Feb 5, but you have HIT in your result, so I'm not sure what the real logic should be.

     

    But my formula above can be tweaked to suit your needs I hope.

     

    If you need a measure, please be more complete in what the true logic is. A measure is a bit harder in this case to build and I don't want to spend time going down the wrong path. 

2 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion

    This calculate column will get you started, but your logic isn't complete.

     

     

    Calculated Column = 
    IF(
        [Date Act] > [Date Referred],
        IF(
            [Date Act] > [Date Referred]+3,
            "Hit",
            "Miss"
        ),
        IF(
            [Date Act] < [Date Referred] - 3,
            "Hit",
            "Miss"
        )
    )

     

     You didn't explain why the bottom two comparisons should have a different calculation than the top two, and your third one is wrong. Date.Act is Feb 8. If Date.Act (feb 8 )is less than Date.Referred (Feb 8 )-3 (now Feb 5) then hit else miss. That is a MISS because Feb 8 is not less than Feb 5, but you have HIT in your result, so I'm not sure what the real logic should be.

     

    But my formula above can be tweaked to suit your needs I hope.

     

    If you need a measure, please be more complete in what the true logic is. A measure is a bit harder in this case to build and I don't want to spend time going down the wrong path. 

    • Anonymous's avatar
      Anonymous
      Not applicable

      oh OK...

      let me try

       

      will mark ur reply as solution once im done