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.
Hello!
I've got a table with a series of months and values, like this:
Customer | Agreement start date | Type | Date | Calculated column I want: latest start date where type = new acquisition and customer = same |
1234 | 1/6/2020 | New Aquisition | 1/1/2021 | 1/6/2020
|
1234 | Renewal | 1/1/2022 | 1/6/2020 | |
1234 | 1/6/2022 | New Acquistion | 1/1/2023 | 1/6/2022 |
1234 | 1/6/2022 | Renewal | 1/1/2023 | 1/6/2022 |
4414 | 2/7/2022 | New Acquisition | 1/1/2023 | 2/7/2022 |
It needs a calculated column because I'm trying to make it a matrix, where the rows are the start dates and the columns are the dates.
I've tried this, but get a circular dependency:
Solved! Go to Solution.
Hi @joetech
Try this, create the column
Test =
var _latest=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Customer]=EARLIER('Table'[Customer]) && 'Table'[Date] <= EARLIER('Table'[Date]) && 'Table'[Type] = "New Acquisition"))
return CALCULATE(MAX('Table'[Agreement startate ]),FILTER(ALL('Table'),'Table'[Customer]=EARLIER('Table'[Customer]) &&'Table'[Date]=_latest && 'Table'[Type] = "New Acquisition"))
result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @joetech
Try this, create the column
Test =
var _latest=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Customer]=EARLIER('Table'[Customer]) && 'Table'[Date] <= EARLIER('Table'[Date]) && 'Table'[Type] = "New Acquisition"))
return CALCULATE(MAX('Table'[Agreement startate ]),FILTER(ALL('Table'),'Table'[Customer]=EARLIER('Table'[Customer]) &&'Table'[Date]=_latest && 'Table'[Type] = "New Acquisition"))
result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Perfect. great job.
Hi @joetech
Try this:
Column =
VAR _A =
FILTER (
ALL ( 'table' ),
'table'[Customer] = EARLIER ( 'table'[Customer] )
&& 'table'[Type] = "New Aquisition"
)
RETURN
MAXX ( _A, [Agreement start date] )
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi,
In this case, the first two rows are reporting the wrong dates, as the start date for the first row in your custom column is later than the agreement start date of that row.
Hi @joetech
You said, you want: latest start date where type = new acquisition and customer = same
four first rows in the table have the same customer number! so please check your post and update that with new description
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
@joetech you can utilize this either as a measure or calculated column
Column =
VAR _mxCustomer = calculate(MAX(tbl[Customer]))
VAR _mxDate = CALCULATE(MAX(tbl[Agreement start date]),FILTER(ALL(tbl),tbl[Customer]=_mxCustomer&&tbl[Type]="New Acquisition"))
RETURN _mxDate
pbix is attached
Hi,
In this case, the first two rows are reporting the wrong dates, as the start date for the first row in your custom column is later than the agreement start date of that row.
if a customer has 2 "New Acquisitions" types, they'll have 2 different start dates.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |