Apply datediff over all dates in my date table

[martinch]

I am trying to create a datediff function that gives me the datediff between my event_created date (static) and all dates in my date table based on the start of week column in my date table. When I try to get a date diff for all dates in my date table, I only get the date diff for the week of my event created date. I'd like it to calculate the date diff for all of the starts of the week in my date table.

[amitchandak]

@martinch , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.

Based on what I got

event Week Start date = 'Table'[event_created date]+-1*WEEKDAY('Table'[event_created date],2)+1 //monday

or

event Week Start date = 'Table'[event_created date]+-1*WEEKDAY('Table'[event_created date],1)+1 //Sunday

need for something else

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1

new column

date diff=datediff([event Week Start date],date,WEEK)

date diff=datediff([event Week Start date],date,Day)

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[martinch]

@amitchandak 

Let me try this again. So, I'm trying to create a measure. I'm looking to create something like this: 

				start week	start week	start week	start week	start week
id	datereported	dateclosed	datediff	10/28	11/4	11/11	11/18	11/25
1	10/28	11/19	22	0	7	14	21	28
2	10/28	11/4	7	0	7	14	21	28
3	10/28	1/18	82	0	7	14	21	28
4	10/28	12/2	35	0	7	14	21	28
5	10/28	10/31	3	0	7	14	21	28
6	10/28	11/6	9	0	7	14	21	28

However, based on my measure -

Measure =

CALCULATE(

   DATEDIFF(MAX(Table[DateReported]),MAX('Date'[Startofweek]),DAY),ALL('Date'))

My values all equal 369 instead of the datediff I'm looking for. Ultimately, I'll create a measure that says if the measure[datediff] > datediff(datereported,dateclosed,day) then 1 else 0 so I can figure out how many tickets were still open on the first of the week. 

I hope this makes sense. 

Thank you so much for your response and your help. I really appreciate it. 

Regards, 

martinch

[Anonymous · Accepted solution]

Hi @martinch 

I think you can build a calendar table and use measure to achieve your goal.

I build a sample table like yours and add a datediff column in it.

Datediff = DATEDIFF('Table'[datereported],'Table'[dateclosed],DAY)

Then build a date table.

Date = 
VAR _T = ADDCOLUMNS(CALENDAR(MIN('Table'[datereported]),MAX('Table'[dateclosed])+7),"WeekDay",WEEKDAY([Date],2))
VAR _T2 = SUMMARIZE(FILTER(_T,[WeekDay]=1),[Date])
VAR _ID = VALUES('Table'[id])
VAR _T3 = GENERATE(_ID,_T2)
Return
_T3

Measure:

Datediff = 
Var _Datediff = DATEDIFF(CALCULATE(MAX('Table'[datereported]),FILTER('Table','Table'[id]=MAX('Date'[id]))),MAX('Date'[Date]),DAY)
return
IF(_Datediff>CALCULATE(MAX('Table'[Datediff]),FILTER('Table','Table'[id]=MAX('Date'[id]))),1,0)

Build a matrix visual by date table and measure.

Build a matrix visual by table and group two matrix visuals.( turn off word wrap in row header and column header in Format)

You can download the pbix file from this link: Apply datediff over all dates in my date table

Best Regards,

Rico Zhou

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