Forum Discussion

harshad_barge's avatar
harshad_barge
Icon for Helper I rankHelper I
6 years ago
Solved

DAX incorrectly calculating DATEADD condition

Hi all, 
I am still discovering DAX and I am incredibly grateful for the support of this forum so far. I have a problem I dont know why i am not getting an output for just a few of the values. I can really use your help!
I have the following DAX:

 

Position From (Year) = IF('Ordinary Hours'[Calculation Date].[Date] < DATEADD('Ordinary Hours'[Position From Date].[Date],1,YEAR), "Y1",
IF('Ordinary Hours'[Calculation Date].[Date] < DATEADD('Ordinary Hours'[Position From Date].[Date],2,YEAR), "Y2",
IF('Ordinary Hours'[Calculation Date].[Date] < DATEADD('Ordinary Hours'[Position From Date].[Date],3,YEAR), "Y3",
IF('Ordinary Hours'[Calculation Date].[Date] < DATEADD('Ordinary Hours'[Position From Date].[Date],4,YEAR), "Y4",
IF('Ordinary Hours'[Calculation Date].[Date] < DATEADD('Ordinary Hours'[Position From Date].[Date],5,YEAR), "Y5",
IF('Ordinary Hours'[Calculation Date].[Date] < DATEADD('Ordinary Hours'[Position From Date].[Date],6,YEAR), "Y6",
IF('Ordinary Hours'[Calculation Date].[Date] < DATEADD('Ordinary Hours'[Position From Date].[Date],7,YEAR), "Y7",
IF('Ordinary Hours'[Calculation Date].[Date] < DATEADD('Ordinary Hours'[Position From Date].[Date],8,YEAR), "Y8",
IF('Ordinary Hours'[Calculation Date].[Date] < DATEADD('Ordinary Hours'[Position From Date].[Date],9,YEAR), "Y9",
"NA")))))))))

The columns are date time columns as below:

 

I am getting NA as output for these values when it should be "Y1"
What am I doing wrong here? or is DAX calculating it incorrectly?
Ashish_Mathur  danextian  amitchandak Greg_Deckler  PaulDBrown ImkeF MFelix 
Thanks,

Harshad

Need Help
  • danextian's avatar
    danextian
    6 years ago

    Hi harshad_barge ,

     

    If you are simply trying to get the difference in years between two dates, you can just use DATEDIFF().

    Position from (Year) =
DATEDIFF ( 'Table'[Calculation Date], 'Table'[Position From Date], YEAR )

     

4 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    6 years ago

    Hi harshad_barge ,

     

    If you are simply trying to get the difference in years between two dates, you can just use DATEDIFF().

    Position from (Year) =
DATEDIFF ( 'Table'[Calculation Date], 'Table'[Position From Date], YEAR )

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    harshad_barge 

    create a datediff column in year and month and the use switch true(). Both as column

     

    diff = datediff('Ordinary Hours'[Calculation Date],'Ordinary Hours'[Position From Date],Year)

switch(true(),
diff < 1, "Y1",
diff < 2, "Y2",
diff < 3, "Y3",
diff < 4, "Y4",
diff < 5, "Y5",
diff < 6, "Y6",
diff < 7, "Y7",
diff < 8, "Y8",
diff < 9, "Y9",
"Y10")
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    Write this calculated column formula

    =IF('Ordinary Hours'[Calculation Date] < DATEADD('Ordinary Hours'[Position From Date],1,YEAR),"Y1",BLANK())