Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
If this post helps, then please consider accepting 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 :).
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?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |