Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dax Commands with Date vs Days

I am trying to get the Number of Days between two Dates, then if <= 30, I want the Original Amount to show in the New Current Column.  If >= 30 AND <=60, then the Original Amount show in the New 31-6...
aging
Date DIFF
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, here’s my solution.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous  you can simply do this and DAX will comply

 

 

 but to answer your question

_dateDiffDateDay = DATEDIFF('Table'[Column1].[Date],'Table'[Column2].[Date],DAY)
with this DAX does exacty same as
'Table'[Column2]-'Table'[Column1]


_dateDiffDayDay = DATEDIFF('Table'[Column1].[Day],'Table'[Column2].[Day],DAY)
with thi DAX is only subtracting the DAY part (31-1),(30-1)

 

pbix is attached

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you for the info, but what I'm trying to do is have DAX calculate the DAYS between Invoice Date and New Date Paid off.  Once it gets those days, if <= 30, then I want to take the Original Amount and it put it in the New Current Column.   

 

  • New Current = IF(DATEDIFF([Invoice Date].[Date], [New Date Paid Off].[Date] <= 30, DAY), Exceptions[Original Amount],0)

 

For the New 31-60 Column, I want DAX to determine the days between Invoice Date and New Paid Off Date and if GREATER >= 31 and <=60 DAYS,  then it would take the Original Amount an add to this new column New 31-60.

 

  • New 31-60 = IF(DATEDIFF([Invoice Date].[Date] >= 31, [New Date Paid Off].[Date] <= 60,DAY), Exceptions[Original Amount],0) OR
  • New 31-60 = IF(DATEDIFF([Invoice Date].[Date], Exceptions[New Date Paid Off] >= 31, DAY), IF(DATEDIFF([Invoice Date].[Date], [New Date Paid Off].[Date] <= 60,DAY), Exceptions[Original Amount],0))  - I think I need an AND function in this DAX function.

When I do my DAX queries above, I get the same amount in each aging bucket column, but I was hoping DAX could determine the days between then move the Original Amount into the correct column: