POWER BI - How to extrapolate CHANGES only between 1 scheduled data dump and a 2nd data dump? HELP!

[justin8260]

Hi guys,

not sure if Power BI has been used for the purpose that we intend to use it for, so I'm hoping some pro's may have some opinions / tips / advise they may wish to share on whether it is a smart solution.

We currently operate an HR system, which has it's own inbuilt reports.  These reports cover Core HR data, but also employee benefits data, specifically they pull CHANGES in employee benefits each month (e.g. cover level going from single to married, employee address changing etc), so we can pass this onto the various benefit providers e.g. BUPA etc.

The reports are somewhat flawed, and also, there is frequently a requirement to adjust them, and this proves extremely challenging to implement in a company with various entities / locations.  Therefore, we are looking at Power BI as an alternative solution.

THE WAY FORWARD ? (We hope)

- Set up a recurring data dump from our HR system.  This will will include every single employee and their benefits data in detail, so will have possibly upwards of 80 columns of data per person.

- now the tricky part...the benefit providers will not accept a full listing of employees and benefits details, for them to have to somehow establish the CHANGES for that month.

-So, can Power BI somehow be utlised / setup in such a way that it pulls employee benefits CHANGES ONLY from somehow cross referencing the data dump from 1 month to another? I am very new to Power BI, but I cannot see how do achieve such a thing.

Any advice much appreciated, even in terms of alternative solutions or ideas.

Thanks in advance!

[justin8260]

Hi guys,

not sure if Power BI has been used for the purpose that we intend to use it for, so I'm hoping some pro's may have some opinions / tips / advise they may wish to share on whether it is a smart solution.

We currently operate an HR system, which has it's own inbuilt reports.  These reports cover Core HR data, but also employee benefits data, specifically they pull CHANGES in employee benefits each month (e.g. cover level going from single to married, employee address changing etc), so we can pass this onto the various benefit providers e.g. BUPA etc.

The reports are somewhat flawed, and also, there is frequently a requirement to adjust them, and this proves extremely challenging to implement in a company with various entities / locations.  Therefore, we are looking at Power BI as an alternative solution.

THE WAY FORWARD ? (We hope)

- Set up a recurring data dump from our HR system.  This will will include every single employee and their benefits data in detail, so will have possibly upwards of 80 columns of data per person.

- now the tricky part...the benefit providers will not accept a full listing of employees and benefits details, for them to have to somehow establish the CHANGES for that month.

-So, can Power BI somehow be utlised / setup in such a way that it pulls employee benefits CHANGES ONLY from somehow cross referencing the data dump from 1 month to another? I am very new to Power BI, but I cannot see how do achieve such a thing.

Any advice much appreciated, even in terms of alternative solutions or ideas.

Thanks in advance!

[Anonymous]

Hi @justin8260,

Are there any data sources in your scenario store the employee benefits changes? If there are CHANGES source, you are able to import data from the source to Power BI, and display these changes via reports. And you are able to create dashboard for these reports and share the dashboard to other users. Also you can set schedule refresh for your dataset in Power BI, when changes occur in your data source, the reports in Power BI can updated based on the refresh schedule.

However, if there are no CHANGES source, you would need to import employee benefits data of each month to Power BI, and make reports about monthly change based on the imported data.

Thanks,
Lydia Zhang

[justin8260]

Hi Lydia,

thanks for the reply!

in response to your first question, yes and no.  Yes, we have employee change reports for each employee benefit, but these are highly unreliable to say the least.  Even if they were reliable, the whole reason we are looking to use Power BI is so that we can have a means of creating a suite of reliable and importantly modifiable reports, pulling from one large repeated data dump.  If in order for this to work, we have to use all the CHANGE reports from our HR system as mini data dumps, we may as well not bother with Power BI if you know what I mean.  We have no need to use the dashboard side of things, or create fancy graphs/charts, the output required is purely a basic excel spreadsheet of data showing CHANGES only.

So, i think we need to focus on your 2nd sentence.  Is it possible to create reports about monthly change based on the imported data, and if so, how would one begin about achieving this? so as to only report on CHANGES.  Some example changes:

- employee left company (should not appear on the most recent employee CHANGES benefits report

-employee joined. (did not appear on previous employee CHANGES benefits report, but should appear on most recent)

- employee changes cover level (employee should appear on most recent report with new cover level reflected)

- Employees with absolutely no changes should NOT appear on the monthly benefits changes report.

Thanks all for any input

[Anonymous]

Hi @justin8260,

Please post the raw data of your tables in different months.

Thanks,
Lydia Zhang

[justin8260]

Sorry, but how do you attached a spreadsheet/table on here? thanks!

[justin8260]

meantime, to simplify the data in question:

Group Life Assurance

February

Employee number       Name        Salary          Address          Cover Level             Cost

1                                    EE1             £50,000      Street1           6x Basic salary         £100                

 2                                     EE2             £60,000      Stree2             4x Basic salary         £50

3                                    EE3             £20,000      Street3            2x Basic salary         -£25

4                                    EE4             £30,000      Street4            8x Basic salary         £200

March

Employee number       Name        Salary          Address          Cover Level             Cost

1                                    EE1             £42,500     Street1           5xBasic Salary   £75                                                                      2                                    EE2             £60,000      Stree2            4x Basic salary         £50

3                                    EE3             £20,000      Street3           2x Basic salary         -£25

4                                    EE4             £30,000      Street99          8x Basic salary         £200

Note the changes to employee 1 and employee 4.

[Anonymous]

Hi @justin8260,

Create a new column using the following DAX, then set value of the column  to False to filter the table visual.

Column = IF(Mar[Salary]=RELATED(Feb[Salary])&&Mar[Address]=RELATED(Feb[Address])&&Mar[Cover Level]=RELATED(Feb[Cover Level])&&Mar[Salary]=RELATED(Feb[Salary]),TRUE(),FALSE())


Thanks,
Lydia Zhang

[justin8260]

thanks Lydia - a bit like excels concatenate combined with lookup solution.

How would this solution deal with the scenario whereby some of our data dumps would have multiple rows per employee (to detail their dependants data) ? so we may have employee 1 appear 3 or 4 times, with exactly the same data as the first row for that employee, excluding the dependant data which would differ and would detail each member of the family. I know in Excel, that wouldn't work as the lookups would struggle to know which line to compare against which (without making a more specific unique identifier...but then the solution becomes even more complex).

Thanks

J

[Anonymous]

Hi @justin8260,

What expected result do you want to get when you have multiple rows per employee ? The above DAX formula should also work in this scenario.

Thanks,
Lydia Zhang

[justin8260]

Hi guys, not sure if Power BI has been used for the purpose that we intend to use it for, so I'm hoping some pro's may have some opinions / tips / advise they may wish to share on whether it is a smart solution.

We currently operate an HR system, which has it's own inbuilt reports. These reports cover Core HR data, but also employee benefits data, specifically they pull CHANGES in employee benefits each month (e.g. cover level going from single to married, employee address changing etc), so we can pass this onto the various benefit providers e.g. BUPA etc.

The reports are somewhat flawed, and also, there is frequently a requirement to adjust them, and this proves extremely challenging to implement in a company with various entities / locations. Therefore, we are looking at Power BI as an alternative solution.

THE WAY FORWARD ? (We hope) - Set up a recurring data dump from our HR system. This will will include every single employee and their benefits data in detail, so will have possibly upwards of 80 columns of data per person.

- now the tricky part...the benefit providers will not accept a full listing of employees and benefits details, for them to have to somehow establish the CHANGES for that month.

-So, can Power BI somehow be utlised / setup in such a way that it pulls employee benefits CHANGES ONLY from somehow cross referencing the data dump from 1 month to another? I am very new to Power BI, but I cannot see how do achieve such a thing. Any advice much appreciated, even in terms of alternative solutions or ideas.

Thanks in advance!