First and second year costs after start date contract

[Anonymous]

 Hi all, I have the following challenge:

Table1:

ContractNr | StartDate | EndDate

Table2:

ContractNr | Customer | Costs

Desired result (ContractNr as slicer):

          | Sum of Costs | Count Customer

Year1 | xx | xx

Year2 | xx | xx

I can make 4 separate measures to calculate each number but I can't make the desired matrix..

Is it possible to make a dynamic Date array based on the start date of the contract to evaluate in a measure to calculate the costs and count of the customers. 

Thanks in advance for the help.

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

I tried to create a similar sample to the above description, but monthly basis, because I did not create more than two years of sample data.

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

all measures are in the sample pbix file.

https://www.dropbox.com/s/yuxazvn0jivyj7x/Rodentje.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

@Jihwan_Kim  Hi there, thanks for your answer. 

I have the same report at the moment but I want to group it by first year cost. So if the contract starts 5-3-20, then first year should group all the cost from 5-3-20 to 5-3-21. Etc for other contracts which all have a different starting date. In that way we can compare the first year costs over the contracts.

Hopefully you can understand what i'm trying to do :).

[Jihwan_Kim]

Hi,

I am not quite sure if I understood correctly, and I don't think my sample suits your case.

Because the cost in my sample only shows year granularity.

Or,

Do you want to do something like cohort grouping? 

[amitchandak]

@Anonymous , refer in my blog on the similar topic

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[Anonymous]

Hi @amitchandak ,

I checked the topic but I want to group on years instead of averaging out the costs between start and enddate over the time..