datediff

D

dida

New Member
Joined
Jun 27, 2011
Messages
15
[FONT=&quot] I Have one table of calendar in the query simply dates. another table of projects and start date of the projects.[/FONT]
[FONT=&quot]I wish to choose the project and then get in the calendar table another columns that compare the chosen project start date to the date in the calendar/ I could not find any way to do it. date diff doesn't not work with chosen from slicer[/FONT]
[TABLE="class: mce-item-table"]
<tbody>[TR]
[TD]PROJ[/TD]
[TD]date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]13/01/2016[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]19/01/2016[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]04/01/2016[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot] [/FONT]
[TABLE="class: mce-item-table"]
<tbody>[TR]
[TD]date[/TD]
[TD]date diff from project[/TD]
[/TR]
[TR]
[TD]01/01/2016[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]02/01/2016[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]03/01/2016[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]04/01/2016[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]05/01/2016[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]06/01/2016[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07/01/2016[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]08/01/2016[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]09/01/2016[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10/01/2016[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11/01/2016[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12/01/2016[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Special-K99 said:
What output are you expecting, examples please.
Click to expand...

if I choose in the slicer project A I wish to get the gap in days from the starting date of project A as follow
[TABLE="width: 215"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]date[/TD]
[TD]date diff from project[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2016[/TD]
[TD="align: right"]-12[/TD]
[/TR]
[TR]
[TD="align: right"]02/01/2016[/TD]
[TD="align: right"]-11[/TD]
[/TR]
[TR]
[TD="align: right"]03/01/2016[/TD]
[TD="align: right"]-10[/TD]
[/TR]
[TR]
[TD="align: right"]04/01/2016[/TD]
[TD="align: right"]-9[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2016[/TD]
[TD="align: right"]-8[/TD]
[/TR]
[TR]
[TD="align: right"]06/01/2016[/TD]
[TD="align: right"]-7[/TD]
[/TR]
[TR]
[TD="align: right"]07/01/2016[/TD]
[TD="align: right"]-6[/TD]
[/TR]
[TR]
[TD="align: right"]08/01/2016[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD="align: right"]09/01/2016[/TD]
[TD="align: right"]-4[/TD]
[/TR]
[TR]
[TD="align: right"]10/01/2016[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD="align: right"]11/01/2016[/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD="align: right"]12/01/2016[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD="align: right"]13/01/2016[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]14/01/2016[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]15/01/2016[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]16/01/2016[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]17/01/2016[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]18/01/2016[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]19/01/2016[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]20/01/2016[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]21/01/2016[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]22/01/2016[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]23/01/2016[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
With your selected project in D1
and your tables in A1 and A6

in B7
=A7-VLOOKUP(D$1,A$2:B$4,2,0)
format column B 2nd table as general, not dates/
 
Upvote 0
if this is a PowerQuery try

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td][/td][td]PowerQuery[/td][td][/td][td][/td][td][/td][td] OR [/td][td]PivotTable with PQ[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Selection[/td][td][/td][td=bgcolor:#70AD47]date.1[/td][td=bgcolor:#70AD47]Subtraction[/td][td][/td][td][/td][td=bgcolor:#DDEBF7]PROJ[/td][td=bgcolor:#DDEBF7]B[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]B[/td][td][/td][td=bgcolor:#E2EFDA]
01/01/2016​
[/td][td=bgcolor:#E2EFDA]
18​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]
02/01/2016​
[/td][td]
17​
[/td][td][/td][td][/td][td=bgcolor:#DDEBF7]date.1[/td][td=bgcolor:#DDEBF7]Subtraction[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]
03/01/2016​
[/td][td=bgcolor:#E2EFDA]
16​
[/td][td][/td][td][/td][td]
01/01/2016​
[/td][td]
18​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]
04/01/2016​
[/td][td]
15​
[/td][td][/td][td][/td][td]
02/01/2016​
[/td][td]
17​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]
05/01/2016​
[/td][td=bgcolor:#E2EFDA]
14​
[/td][td][/td][td][/td][td]
03/01/2016​
[/td][td]
16​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]
06/01/2016​
[/td][td]
13​
[/td][td][/td][td][/td][td]
04/01/2016​
[/td][td]
15​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]
07/01/2016​
[/td][td=bgcolor:#E2EFDA]
12​
[/td][td][/td][td][/td][td]
05/01/2016​
[/td][td]
14​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]
08/01/2016​
[/td][td]
11​
[/td][td][/td][td][/td][td]
06/01/2016​
[/td][td]
13​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]
09/01/2016​
[/td][td=bgcolor:#E2EFDA]
10​
[/td][td][/td][td][/td][td]
07/01/2016​
[/td][td]
12​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]
10/01/2016​
[/td][td]
9​
[/td][td][/td][td][/td][td]
08/01/2016​
[/td][td]
11​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]
11/01/2016​
[/td][td=bgcolor:#E2EFDA]
8​
[/td][td][/td][td][/td][td]
09/01/2016​
[/td][td]
10​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]
12/01/2016​
[/td][td]
7​
[/td][td][/td][td][/td][td]
10/01/2016​
[/td][td]
9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
11/01/2016​
[/td][td]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
12/01/2016​
[/td][td]
7​
[/td][/tr]
[/table]

example excel file
 
Upvote 0
thank you very much but how would it work in power BI? the data I bring from DATABASE to power query and wish to chose the project in the slicer
 
Upvote 0
as I can see you've two tables
so adapt M-code to merge these tables and your slicer will be as selection
 
Upvote 0
sandy666 said:
is that what you want?

example pbix
Click to expand...

AMAZING!!! yes! the final results it's exactly what I needed, I just could not figure how to get to it.
since I have to table one of dates and the other of project and their start date, I saw that you created table tha combined with the larger value, i Mean for each date the project and then the datediff, how? did you did it manualy? becouse I can not, the model will keep getting new projects all the time, secondly, I can not see the measure od the datediff, how did you do it? third, another challenge - each project have exactly different start and end date, the first date is the start date of the project, I used MDX calendar and thought of filtering later bigger then 0... but maybe its not the best way, since the calendar MDX you can choose start date and end date. the reason is that in the end I need graph that start in different date for each project that I chose in the slicer, but if you could answer only on the first and second I will so appreciate it!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Creating a CUBESET function that returns a cube filtered by a date range
Replies
8
Views
2K
Andy Somnifac
A
A
  • Question Question
PQ Add Dynamic "Average" Column
Replies
1
Views
203
Matt Allington
M
G
  • Solved
Power Query? Changing Format of Table
Replies
3
Views
368
RoryA
RoryA
R
  • Question Question
Dynamic Unit Price DAX Measure
Replies
0
Views
236
radonwilson
R
K
  • Question Question
Power bi Calculate the Monday dates
Replies
1
Views
156
GraH
GraH

Forum statistics

Threads
1,224,891
Messages
6,181,614
Members
453,057
Latest member
LE102024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top