Recently, a situation was faced, where date entries for the service job dates and invoiced dates are different for same job based on department (department id is available only in Jobs table). The requirement is to find the revenue based on invoiced dates and jobs based on service job dates with the help of single Date dimension. Find the problem, challenge, and solution approach below.
Sample Data:
Let us understand the sample data by snippets:
a. Department Table:
b. Jobs Table:
c. Invoice Table:
Problem:
Calculate the Revenue Amount from Invoice table, slice and dice the amount by dates and department.
Challenge:
Data modeling is bit tricky as department information is not available in invoice table if invoice table is connected to the jobs table and therefore jobs table with Date table (created dimension in Power BI). In this case the 1st of Jan would give some invoice amount because, the amount numbers are flown from JobId from jobs table and ScheduledOn from date table.
Find the data model and sample results for above mentioned approach below,
1. Data model:
2. Sample results:
As the result is not desirable, the solution model approach must be changed.
Solution Approach for desirable result:
We found the problem in initially phase itself that is unavailability of the department information in invoice table. If this information is available, then Revenue report is a separate entity from Service Jobs report.
Now, department information can be brought from Jobs table with the help of JobId, this can be achieved by calculated column using DAX.
Calculated Column:
LOOKUPVALUE DAX function:
Returns the value for the row that meets all criteria specified by one or more search conditions.
Syntax:
LOOKUPVALUE(
<result_columnName>,
<search_columnName>,
<search_value>
[, <search2_columnName>, <search2_value>]…
[, <alternateResult>]
)
| Term | Definition |
| result_columnName | The name of an existing column that contains the value you want to return. It cannot be an expression. |
| search_columnName | The name of an existing column. It can be in the same table as result_columnName or in a related table. It cannot be an expression. |
| search_value | The value to search for in search_columnName. |
| alternateResult | (Optional) The value returned when the context for result_columnName has been filtered down to zero or more than one distinct value. When not provided, the function returns BLANK when result_columnName is filtered down to zero value or an error when more than one distinct value. |
Now, create calculated column in invoice table as below, this will get the DepartmentId from Jobs table based on the JobId (between Invoice table and Jobs table).
DepartmentId = LOOKUPVALUE(Jobs[DepartmentId], Jobs[JobId], Invoice[JobId])
Restructure the Data Model as below i.e. SoldOn column with Date column, Department Id between Invoice and Department tables, this will provide the separate entity for Revenue Amount calculation.
Let us bring the department, date, and amount column into table visual from department, date, and invoice tables respectively.
The above visual gives the correct values based on the dates and department.
Hope you have learnt the way to figure out the solution. Thanks.
Srikanth K
Data Analyst
Addend Analyst