Date Difference

[gauravnarchal]

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.

[v-jayw-msft · Accepted solution]

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

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[vivran22]

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 Twitter

[jairoaol]

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.

[TomMartens]

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

[pranit828]

Hi @gauravnarchal 

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.

[gauravnarchal]

@pranit828 - Instead of creating column can this be achieved with the measure?

[pranit828]

HI @gauravnarchal 

Yes, you can use measure to create it.

[gauravnarchal]

@pranit828  - I am not getting the table when creating the measure. Is there something wrong I am doing?

See below screenshot.