01-06-2017 11:00 PM
I'm trying to create a method of calculating job costs per job. I've been successful doing this with excel, but I'm moving everything into the SQL Database and trying to replace my lookup functions with SSIS/ETL through SSDT. I've created the necessary Tasks and everything is loading correctly from my flat files into SQL and finally into Power BI. The problem is that I no longer want to pre-calculate my labor costs, taxes, L&I, and Overhead in an excel file since this seems to defeat the purpose of "automation". I know that Power BI has some complex abilities with nested DAX formulas and I'm trying to use them to replace the calculations from the excel file. Basically I had a table for all of the changing rates (listed earlier in this paragraph) and I was using "=vlookup(...)" function in excel to calculate the appropriate rates with the appropriate dates. One sample would be the hourly rates. Employees get raises, and data 6 months before that rely on their previous hourly rate, so I need to match the rows date with the effective hourly rate at the time. I'm not sure if this process is best made in SSIS or in Power Bi (measures, custom columns, etc..) or lastly pre-ETL (excel). The data is coming from two sources (regarding hourly rate): SQL Database (Hourly Rate, Effective Date, Employee ID; Employee ID, Employee Name) and a flat CSV file that is ETL'd through SSIS into SQL Database (Name, Date, Hours, Job #, etc...).
I need somehow to have Power BI or SSIS add this column or lookup up the appropriate hourly rate so that I could create a custom column or measure to be able to multiply the two columns to compute the gross labor cost per line and all of the other costs. For some reason I, hitting a wall regarding which formulas to use. I'm still adapting and learning DAX (used to excels nested formulas).
Anything helps, even if someone points me in the right direction,
Solved! Go to Solution.
01-11-2017 12:40 PM
I found the solution!!
01-07-2017 02:01 AM
You can use LEFT OUTER JOIN in the Query Editor ( Merge Queries) or LOOKUPVALUE function in DAX.
You can also refer to Ken Puls blog for this:
Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
01-10-2017 11:48 PM
On Power BI side, you can create a calculated column or measure use LOOKUPVALUE Function (DAX). It would be better if you could share some sample data for our analysis.
If you want to find the replacement of VLOOKUP in SSIS, you can post a thread in SQL Server Integration Services forum.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
01-11-2017 08:52 AM
Here is the sample data for the employee Id data that I relate the data to.
Here is the Employee Rate Change Table that is linked to Access and soon to be sharepoint form that generates employee raises and records the logs of when the raise became effective:
Here is the labor data that needs to have the above data populated from the other tables. I would prefer to take care of this in the SQL database first and then upload it to Power BI, but I don't mind doing some of the calculations in Power BI if i have to:
Lastly, the end result should have all of these columns populated but if i successfully populate the data for the hourly rate then i should be able to replicate the same thing for the rest of the data since some of the data is static or is only "looking up" one criteria (where hourly rate is looking up employee name and date):
|Name||Date||Start Time||End Time||Hours||Job #||Note||Client Name||Rate||Gross Labor Cost||L&I Rate||L&I T Cost||Tax Rate||Total Taxes||T Labor Cost||OH Rate||T OH Cost|
01-11-2017 12:40 PM
I found the solution!!
01-11-2017 01:34 PM
I think the formula for the BI calculated column for the hourly rate would be
Rate = CALCULATE (
Max ( 'Employee Rate Change Table'[Rate]),
FILTER ( 'Employee Rate Change Table',LabourData[Date]>'Employee Rate Change Table'[Effective Date] ))
02-03-2017 10:00 AM
My progress report:
This formula is working great and was able to give me the data that I needed. I did run into an issue but it was only because of my own inexperience. The issue was that there was an error in the SSIS package when i would run it due to not have 2 records of employees to match the data to. Once I created a "multicast" tool to receive the "no-match output" from the "lookup" tool and then after adding a "data viewer", I was able to see the 2 references that were missing. If you run into this issue, just do the same thing and you'll see what data was missing. I'm realizing that SSIS has some powerful tools and options and that I have to instruct the software what to do in any given situation, including errors. Good luck!