Forum Discussion
Date Difference
I need help with the measure to calculate the date difference between two dates in a table.
Once I have the days calculated, I then need to get the results as how many items were shipped between the number of days.
Hi gauravnarchal ,
Check the following measures.
Measure = var datediff = DATEDIFF(SELECTEDVALUE('Table'[booking date]),SELECTEDVALUE('Table'[shipped date]),DAY) return SWITCH(TRUE(),datediff>=1&&datediff<=7,"1-7",datediff>=8&&datediff<=10,"8-10",datediff>=11&&datediff<=13,"11-13",datediff>=13,"13&more") Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[id]),FILTER('Table',[Measure]=SELECTEDVALUE(days[days])))Result would be shown as below.
Best Regards,
Jay
9 Replies
you can create a column
_datediff =
var _diff = DATEDIFF(Table[Booking Date], Table[Shipped Date], DAY)
Return IF([_diff]<=7,"1-7",IF([_diff]<=10,"8-10",IF([_diff]<=13,"11-13","13 and More")))
and create a matrix chart with _datediff and count of _datediff column.
Let em know if you need help projecting it in table.
Anonymous - Instead of creating column can this be achieved with the measure?
Yes, you can use measure to create it.
Hey gauravnarchal ,
You can create a calculated column by using the DAX function DATEDIFF, create a calculated column like so:
days = DATEDIFF( 'Table'[booking date] , 'Table'[shipped date] , DAY)Counting the difference between the booking and shipped date can be solved following the static segmentation pattern that is described by this pattern: https://www.daxpatterns.com/static-segmentation/
Hopefully, this provides some idas on how to tackle your challenge.
Regards,
Tom
the difference in days can be calculated with a measure as explained by previous colleagues with the Datediff() function, but to be able to use day ranges to plot is better a calculated column or table.
Hello gauravnarchal
In such cases, I usually prefer to use Power Query to get the difference of the two dates and then add the column for categories using conditional columns. Power Query is made for such calculations and is efficient as compared to DAX calculated columns.
For getting the difference of the two dates, in the Power Query:
- select the Ship Date & Order date (in that order)
- Go to Add Columns > Date > Subtract Days
- This will add a column with Duration in "Days:Hours:Months:Seconds" format.
- Select the column > Transform > Duration > Total Days
- You will get the column for days difference
For adding categories, you can use the Conditional Column feature under Add Column in Power Query.
For more details, you may follow the articles below:
https://www.vivran.in/post/bi-simplified-webinar-date-transformations-using-power-query
https://youtu.be/r5pVbKQkbGI?t=788
For adding categories:
https://www.vivran.in/post/adding-categories-with-power-query
Cheers!
Vivek
If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on TwitterHi,
Share the link from where i can download your PBI file.
Hi gauravnarchal ,
Check the following measures.
Measure = var datediff = DATEDIFF(SELECTEDVALUE('Table'[booking date]),SELECTEDVALUE('Table'[shipped date]),DAY) return SWITCH(TRUE(),datediff>=1&&datediff<=7,"1-7",datediff>=8&&datediff<=10,"8-10",datediff>=11&&datediff<=13,"11-13",datediff>=13,"13&more") Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[id]),FILTER('Table',[Measure]=SELECTEDVALUE(days[days])))Result would be shown as below.
Best Regards,
Jay
