Multiple data-points for single entity - how to compare some of the data-points within one entity

[Anonymous]

Hello,

I am facing some problems that might be trivial but I got stuck thinking about them.

What I have is:

• one table (Table1) that consists:
  
  • multiple data-points (daily snapshot of a state for a specific) for a single data-entities (identified by an id)

So basically each row have multiple columns that are describing state of that particular data-entity on a given day - from the moment of data-entity creation until today. This means that the ID is not unique for each of the datapoint, but rather a combination of unique ID + date.

Now - what I want to do is the following - I want to track how the list of the unique id's changes between two given dates based on some third column value.

To describe a full problem, let's assume that Table1 consists of:

• Date (which represents the time when the snapshot of data-state was)
• Unique ID (identifying each data-entity)
• Named time period (text field which consists a named time period that a data-entity could be assigned to - they are not strictly bound to any real timeframes like quarters, months etc, so we use named time periods)
• Start of named time period (date/time field representing a specific date that is based on when the named period is starting)
• End of named time period (date/time field representing a specific date that is based on when the named period is ending)

Now, what I want to do is:

• verify if a specific data-entity (based on the unique id) was assigned to the same time period both on the beginning of that period and end of that period - or was it added/removed/changed somewhere in between and add that column/field to the table that could be dynamic

The end effect I'm trying to achieve here is a visual that would be slice'able by the named time periods and would inform me with informations about:

1. What was assigned to that time period at the beginning of it (this I've already achieved)
2. What was assigned to that time period at the end of it (this I've already achieved by simply comparing the End of time period with current date and whether it is being assigned)
3. Here are the data-entities that were assigned to that time period (wasn't present at the beginning, are there at the end);
4. Here are the data-entities that were unassigned from that time period (was present at the beginning, are not there at the end)

I've tried multiple things and I'm pretty sure I might have missed some obvious solution here but my mind is melting right now and I've decided to look for some help 🙂

[bcdobbs]

Without seeing a demo file it's hard to be more specific but if you are able to get a list of Ids at the start and end of the time period which points 1 and 2 suggest you have then you could look at using EXCEPT. 

You could use VALUES and CALCULATETABLE to store the start and end list in variables and then list of Ids for point 3 would be:

EXCEPT ( EndList, StartList)

for point 4 it would be the other way round:

EXCEPT ( StartList, EndList).

Both return tables so you could then use COUNTROWS or something to return data for visual?

[Anonymous]

Thanks for taking a closer look into this! I will test proposed solution today and come back here with some information about whether it has allowed me to do what I've wanted 🙂

[Anonymous]

Thanks for taking a closer look into this! I will test proposed solution today and come back here with some information about whether it has allowed me to do what I've wanted 🙂

[bcdobbs]

If that doesn't help get you going and I've missed the point are you able to share a demo pbix file and a mock up or your desired output?