Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a dataset with Service Call details. It provides information on System ID, date of service and associated costs (labor, material etc.).
A second table has system coverage information. It also has System ID, Contract Name, start and end dates. Challenge is that each system may have 1 to n number of contracts often with overlapping periods as well as periods in between that had no coverage. So, one has to figure it out based on contract end and start dates.
As system IDs are repeated in both tables, I have created a list of unique SystemIds and linked it with both datasets. I also have a standard calendar table in my model. It is only linked to the first table of Service calls with date of Service. I could link the dates to start and end as well but i have been able to use the greater than or equal to function to create some of the measures using the coverage dates.
Here are the two measures I am looking to develop:
1. Contract_Coverage_Months: This is the count of number of months a given system (or group) has been under contract. It needs to exclude months without coverage and also not double count the months if more than 1 contract is in place during a given period.
2. Contract_Spend: This is the sum of costs from the Service call's dataset for only the dates that fall within a contract coverage period. Service is often performed for systems outside the coverage period and those costs need to be excluded.
@ali1234 Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Thanks @Greg_Deckler for the feedback..
I'll try again. Here's the table of data
Table Name: Svc_CallTbl
Asset_ID | Service Call # | Service Call Date | Labor Cost | Material Cost | Total Cost | Region |
Asset123 | Call234 | 03/03/2019 | $100 | $200 | $300 | Europe |
Asset123 | Call345 | 07/21/2019 | $50 | $75 | $125 | Europe |
Asset345 | Call543 | 02/02/2020 | $60 | $80 | $140 | Canada |
Asset123 | Call656 | 03/09/2020 | $20 | $25 | $45 | Europe |
Asset123 | Call565 | 10/1/2019 | $50 | $110 | $160 | Europe |
The second table is for Contract information
Table Name: Contract_Tbl
Asset_ID | Contract Name | Contract Start Date | Contract End Date | |||
Asset123 | Con Coverage1 | 01/01/2019 | 03/31/2019 | |||
Asset123 | Con Coverage2 | 08/01/2019 | 07/31/2020 | |||
Asset123 | Con Coverage3 | 10/01/2019 | 07/31/2020 | |||
Asset677 | Con Coverage1 | 12/01/2019 | 12/31/2020 |
Asset_ID columns are linked in the data model. Also, date column of a standard calendar table is mapped to the Service Call Date column of the first table. I also have a list of unique Asset_IDs in a table that is linked to both the tables above.
Following measures are needed:
NumberOfCoverageMonths & Contract_Period_Costs
If Year 2019 is selected, then output would be as given in the table below. (Note: Asset123 cost is 460 which is the sum of costs incurred in March and October. Cost incurred in July is not included as no active period of contract exists in July)
Asset_ID | NumberOfCoverageMonths | Contract_Period_Costs | ||
Asset123 | 08 | 460 | ||
Asset345 | 00 | 00 | ||
Asset677 | 01 | 00 |
Thanks @Anonymous , I have added additional information.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |