Data Modelling - matrix visualization - weekly/monthly/90 day view

Hi Team,

Had to recreate the post as I am unable to post edits to my previous post.

A power BI novice here.

I am connected to an on prem sql server. I have done some filtering on the data to get it to show as below :

Employee	Project start date	Project end date	Client
John	9/3/2020 8:30:00 AM	9/4/2020 5:30:00 PM	Abc solutions
Beth	9/2/2020 11:30:00 AM	9/2/2020 12:30:00 PM	Omega technologies
Beth	9/2/2020 1:30:00 PM	9/2/2020 5:30:00 PM	Sea technolgies
Mary	9/4/2020 1:30:00 PM	9/4/2020 5:30:00 PM	Horizon solutions

Now i want to convert this data into a weekly view where the weekdays become columns and the corresponding data for clients goes under the appropriate weekday column for that employee as shown below. If the employee doesn't have an engagement for that day then we show them as open. :

employee	8/31/2020	9/1/2020	9/2/2020	9/3/2020	9/4/2020
John	Open	Open	Open	Abc Solutions: 8:30 am - 5 :30 pm	Abc Solutions: 8:30 am - 5 :30 pm
Beth	Open	Open	Omega technologies: 11.30 am – 12.30pm Sea technologies : 1 :30 pm – 5 :30 pm	Open	Open
Mary	Open	Open	Open	Open	Horizon solutions:1:30 pm – 5:30 pm

I have been trying to do this but it's either hardcoding or feels like a hack. I am Almost on the verge of giving up on power bi and trying to build a asp.net. app. I can do this on c# but it's time taking. How does one approach this ? Any suggestions or solutions are highly appreciated.

Need Help

4 Replies

amitchandak
Super User
6 years ago
Anonymous , refer my blog on HR with Start end Date, Than can help a bit

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://youtu.be/e6Y-l_JtCq4
Greg_Deckler
Community Champion
6 years ago
Anonymous - Well, you would need a Date table I think. You would then need something like Open Tickets - https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

Or you may need this thing I just did with Overlap - https://community.powerbi.com/t5/Desktop/Properly-summing-up-time-spent-in-overlapping-appointments/td-p/1327178/highlight/false/page/2

Assuming you have a Week column in the Columns of your matrix and Employee in rows, maybe something like lookup range:

https://community.powerbi.com/t5/Quick-Measures-Gallery/LOOKUPVALUE-Range/m-p/974201#M430

Measure = VAR __Employee = MAX('Table'[Employee]) VAR __Date = MAX('Calendar'[Date]) VAR __Client = MAXX( FILTER( ALL('Table'), [Employee] = __Employee && __Date >= [Project start date] && __Date <= [Project end date] ), [Client] ) RETURN IF(ISBLANK(__Match),"Open",__Client)

Oh yeah, Lookup Range ought to do it!
- Anonymous
  Not applicable
  6 years ago
  I didn't really understand the lookup range concept. Kinda new to Dax and all. So I went ahead with the date table route. Created a datetable and linked it to my data table. Have some basic visualization set up in a matrix. For my company, most of the start and end dates have times from 8:30 am - 5:30 pm. If I want to set up start and end dates along with their times like shown above, should I create a date table with hourly intervals for each day ? Thanks for your help Greg_Deckler
  - Anonymous
    Not applicable
    6 years ago
    Hi Anonymous,
    
    I do not so recommend you accurate mapping all dates with hour intervals, it will generate huge amount of records and affect the performances.
    
    In my opinion, you can create a expand table on date level to mapping raw table records and a standalone time table(it does not have any relationship to other tables) for further dax formula calculation.
    
    You can take a look at the following link to create a calculated table with expanding records which generated from raw table date fields:
    
    Spread revenue across period based on start and end date, slice and dase this using different dates
    Regards,
    
    Xiaoxin Sheng

Forum Discussion

Data Modelling - matrix visualization - weekly/monthly/90 day view

4 Replies

Related content

Modelling

Data Modeling

Cannot Load Model

Semantic Model

Healthcare Semantic Model