DAX measure to find months of coverage and associated Cost

[ali1234]

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. 

[Greg_Deckler]

@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.

[ali1234]

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

[Anonymous]

Need some test data and a clear picture of what your input is and what the output should be. Please see this guide: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[ali1234]

Thanks @Anonymous , I have added additional information.