Passing data lastdate function to linked table.

[jordanwebb10]

Hey everyone! I hope someone can help me out with this problem

Just a little background, I am a strength and conditioning coach trying to utilize Powerpivot to better help my organization and athletes. So please excuse me if my technical skills are a bit behind the usual! In essence I need to pass an athletes most current healthy status to varying tables. Let me dive a bit deeper!

This is a simplified version of my current setup, but it should do to get my point across.

I have 4 tables setup like the following.

TrainingLoad_DT (Training Load Data Table) - The work that the particular athlete did during that practice. And the Length of that practice.

Name_LT - Simply a Roster
Date_DT - Simple Date Table
CurrentStatus_DT - A record of the athletes health status. Simply If they become injured an entry would be put in, once they become healthy another entry would be put in.

I have the tables linked like so.

I have two goals.

1. Pass the most current-status information to the Name_LT. So I would want to add a column 'Name_LT'[Current Status] that then auto updates with the athletes most current status data. I think I can accomplish this by using something like the following.

StatusID.LastDate:=Calculate(Average('CurrentStatus_LT'[Status ID]),Lastdate('CurrentStatus_LT[Date]))

and then somehow pass that to the 'Name_LT'

2. I have no idea how to do this. Pass the date dependent Current-status information to the 'TL_DT'. The idea would be to add a new calc column 'TL_DT'[Current Status] that fits the correct context of 'TL_DT'[Name] and 'TL_DT'[Date]. Essentially I want the table in powerpivot to look like this (example in next post I was only allowed 4 pics, I realize I used an excel table for this example).

The practical goal is that I want to be able to filter out training data when a player injury status is injured/0. In case your wondering a guy could be injured and still participating in practice partially, I need a way to distinguish his status from a data table that is coming in from our medical staff.



Any help would be much appreciated!!! Ive been working on this for days and I am afraid it is just outside of my skill set!


JW

 

[jordanwebb10]

 

[jordanwebb10]

Just In case the pictures aren't showing up.

 

[jordanwebb10]

 

[powerpivotpro]

Jordan this is my kind of problem for sure. Drop me an email? You'll find my address disguised in my signature below

 

[jordanwebb10]

Sent you an Email. I wouldn't mind posting the results here as well so everyone can learn.