how to display

[srini]

Hi

I have a query.

I have two tables one has 

ID START_DT            NAME
1 04/10/2017 00:00 Test1
2 01/01/2017 00:00 Test2
3 06/05/2017 00:00 Test3
4 07/10/2017 00:00 Test4 

Another table like this 

ID START_DT            END_DATE
2 01/01/2017 00:00 06/30/2017 00:00:00
3 06/05/2017 00:00 07/30/2017 00:00:00
1 04/10/2017 00:00
4 07/10/2017 00:00

how do i get a visualization in power bi which shows

if someone selects week1 or week 20, i want to display who people have left (which have end date) and people who are still working who dont have a end date.

I would like to give the week as a parameter or slicer. I could do thsi in SQL as a report, but i was told they want

something visually to see.

Also can the week be changed (if ISO week or our Financial week which starts from April )

Can someone please guide me.

Thanks

Sree

[v-caliao-msft]

@srini,

I have tested it on my local environment, the steps below is for you reference.

1. Create a date table.
   Date = ADDCOLUMNS(CALENDAR(DATE(2017,1,1),date(2017,7,31)),"WeekNumber","Week"&RIGHT("00"&WEEKNUM([Date]),2))
2. Create a measure in Table 2.
   Measure2 =
   var maxdate = MAX('Date'[Date])
   var mindate = MIN('Date'[Date])
   var checkactivepeople = IF((MAX(Table2[START_DT])>=mindate&&MAX(Table2[START_DT])<=maxdate)||(MAX(Table2[END_DATE])>=mindate&&MAX(Table2[END_DATE])<=maxdate)||(ISBLANK(MAX(Table2[END_DATE]))),1,0)
   return checkactivepeople
3. Use this measure in your filter.
   

Results

Regards,

Charlie Liao

[srini]

Hi charlie

Morning

I am not sure if you have seen my earlier post but just a quick question on it

When we select a week, what i need is 

for people active the sql would be - where start_date is lessr than the weekday selectecd ex (05/07/2017) and end_date is null 

for people who have left - it would be start_date is less than (<)  weekday selected and end_Date is <= 05/07/2017

when i select the name of the person, its showing like this, is this right

Thanks

Sree

[v-caliao-msft]

@srini,

for people active the sql would be - where start_date is lessr than the weekday selectecd ex (05/07/2017) and end_date is null  

for people who have left - it would be start_date is less than (<)  weekday selected and end_Date is <= 05/07/2017

When you selected week, there are 5 weekdays, which one do you need to used in you measure?

Regards,

Charlie Liao

[srini]

Is there a better way of selecting a from date  and to date

I mean like between two dates, that would be ideal

Thanks

S

[v-caliao-msft]

@srini,

I mean like between two dates, that would be ideal

If that is the case, then my original expression would work.

Regards,

Charlie Liao

[srini]

I did try your solution, i wanted the names of the person as well and when i select the field to be displayed in the table, i am not sure ifs showing the results correctly as its duplicating if you see the image below

[srini]

Hi Charlie

Can you please let me know on the above

Thanks

S

[srini]

Hi Liao

Thanks very much responding to me.

One more question, when i include the name in the Active People Visual, I get people who have not left also.

Ideally I would like the name of the person as well. I just included the name but it doesnt look right.

Thanks

Sri