datediff

[dida]

[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]

 

[Special-K99]

What output are you expecting, examples please.

 

[dida]

What output are you expecting, examples please.

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]

 

[Special-K99]

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/

 

[sandy666]

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

 

[dida]

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/

thanks but its all would not work in power bi

 

[dida]

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

 

[sandy666]

as I can see you've two tables
so adapt M-code to merge these tables and your slicer will be as selection

 

[sandy666]

is that what you want?

example pbix

 

[dida]

is that what you want?

example pbix

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!