Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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-60 Column.  I'm not getting any DAX error using the below info, but it's showing the Original Amount in both Columns.  What am I doing wrong?  Any help would be appreciated.

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

1 ACCEPTED SOLUTION
Community Support

Hi @ktipton ,

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.

5 REPLIES 5
Community Support

Hi @ktipton ,

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.

Frequent Visitor

This was exactly what I needed.  THANK YOU so much!!

Super User

Hi @ktipton

Try these codes to add those columns:

• New Current = IF(DATEDIFF([Invoice Date][New Date Paid Off]DAY)<=30, Exceptions[Original Amount],0)
• New 31-60 =
Var _DayB = DATEDIFF([Invoice Date], [New Date Paid Off], DAY)
return
IF
(_DayB>=31&&_DayB<=60Exceptions[Original Amount],0)

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

Appreciate your Kudos!!

Super User

@ktipton  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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Frequent Visitor

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] <= 30DAY), 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:

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors