Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all, I have a table with the following data of customer engagements, i.e. projects that they signed on for:
- customer names
- start dates
- realized revenue
- expected revenue
What I would like to display, is a table of all the customers and the realized revenue/expected revenue from their first deal is.
A customer may have 2 engagements that both started on the same date, in that case, I'd like to display the sum of both engagements (the realized and expected revenue)
Here's a mocked up sample of what my data looks like
Customer | Start Date | Earned Revenue | Expected Revenue |
A | 1/1/2018 | 1m | |
B | 2/1/2019 | 2m | |
A | 8/1/2019 | 2m | |
B | 2/1/2019 | 2m | |
A | 1/1/2020 | 4m | |
B | 1/1/2020 | 6m |
In this case, my expected output would be:
Customer A - 1m
Customer B - 4m (since two engagements started on the earliest day)
Solved! Go to Solution.
Hi @Anonymous
Considering you have the belwo input data type in data set(I have changed the Earned revenew column to number data type to get the sum)
And use the below measure
calc =
var MIN_DATE = CALCULATE(MIN('Table'[Start Date]),ALLEXCEPT('Table','Table'[Customer]))
RETURN CALCULATE(SUM('Table'[Earned Revenue]),FILTER(ALLEXCEPT('Table','Table'[Customer]),'Table'[Start Date]=MIN_DATE))
to get the below desired output as you needed.
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Hi @Anonymous
Considering you have the belwo input data type in data set(I have changed the Earned revenew column to number data type to get the sum)
And use the below measure
calc =
var MIN_DATE = CALCULATE(MIN('Table'[Start Date]),ALLEXCEPT('Table','Table'[Customer]))
RETURN CALCULATE(SUM('Table'[Earned Revenue]),FILTER(ALLEXCEPT('Table','Table'[Customer]),'Table'[Start Date]=MIN_DATE))
to get the below desired output as you needed.
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
User | Count |
---|---|
93 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
112 | |
97 | |
95 | |
64 | |
58 |