cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

how to display

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

8 REPLIES 8
Highlighted
Microsoft
Microsoft

Re: how to display

@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.
    Capture.PNG

Results
Capture1.PNGCapture2.PNG

 

Regards,

Charlie Liao

 

Highlighted
Frequent Visitor

Re: how to display

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

 

 

 

Highlighted
Frequent Visitor

Re: how to display

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

pbi.png

 

 

 

 

 

Thanks

 

Sree

Highlighted
Microsoft
Microsoft

Re: how to display

@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

 

 

 

 

 

Highlighted
Frequent Visitor

Re: how to display

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

 

 

Highlighted
Microsoft
Microsoft

Re: how to display

@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

Highlighted
Frequent Visitor

Re: how to display

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

 


pbi.png

Highlighted
Frequent Visitor

Re: how to display

Hi Charlie

 

Can you please let me know on the above

 

Thanks

 

S

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors