Hello,
I would like to ask for help with my task. My job is to monitor the number of IDs that work for companies and analyze their movements across the market and companies.
I provide sample data as pbix > https://drive.google.com/file/d/1uBWTbATMSi_OJrlu4KBnhzJXCOBEI4FI/view?usp=sharing
In my sample data I have dates (always the last day of the month), subject ID and a name of the company the ID works for. I need a table with months and years on the rows and in columns I need measures. For every month I need:
- Dictinct count of IDs that were active that month (active means that I find the ID in the sample data) - easy I have that
- Net change in IDs count (some IDs left and new IDs come) - I have that
- New IDs - I think I have that 🙂
- Left IDs - I think I have that 🙂
The problems come here:
1.Question
There are two ways how ID can come into sample data – there can be completely new ID that comes (it is not found in the previous month in the sample data) or it can come as a change from one company to another (ID was in the sample data in the previous month but in current month it is registered under different copmany).
Same for leaving the company. The ID can leave for good (it can not be found in the next month data), or it can change the company, it is found it the next month but under different company. So I need a table showing on rows the dates (months and years) and in the columns:
- New ID count – number of IDs that come as a completely new ones (not in previous month)
- New as a change – IDs that existed before but changed the company
New ID count + New as change = New IDs
- Left ID count – IDs that left all the companies in sample data (not in next month)
- Left as a change – IDs that exist in next month but changed the company
Left ID count + Left as change = Left IDs
New IDs – Left IDs = Net difference
I figured everything except of the changes of the companies, it seems I have a mistake somewhere 😊
For example – ID A101 works for Apple in September and October 2021, however in November 2021 it works for Microsoft, so the net change of IDs is the same, however the ID changed the company, so there should be one for New as change. Same for B102 – during September and October it works for Microsoft, however in November 2021 it works for Apple. So again, it should be counted as New as change. Same goes for IDs that left.
- Question
And the second problem I have is that when I figure the changes of IDs I need to somehow show from which company the ID came or where it went. So for example – I will have to show how many IDs came to Apple over time from other companies. Or how many IDs Appple lost over time to different companies. The emphasis is not just on counts but mainly from which companies IDs comes and where it goes. So my though is that I will choose a company and date in a slicer, for example Microsoft in November 2021 and there will be a table which shows, that there was one ID (A101) that came to Microsoft in November 2021 as a change and the change was from Apple.
I hope I explained it well.
Thanks in advance for any help 😊 I really appretiate it 🙂
Tereza
