Forum Discussion
First and second year costs after start date contract
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.
5 Replies
- amitchandak
Super User
Anonymous , refer in my blog on the similar topic
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..
- 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.
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?
