topic Re: Max Date of a previous month based on condition in DAX Commands and Tips

Max Date of a previous month based on condition

Anonymous — Wed, 08 Dec 2021 16:12:34 GMT

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:

Last Date =

VAR Current_Date = 'table'[Agreement Start Date]

VAR Current_Name = 'table'[customer]

RETURN

CALCULATE( MAX('table'[agreement start date]),

'table'[Agreement Start Date] < Current_Date,

'table'[customer] = Current_Name, 'table'[type] = "New Acquisition")

Do I need to make a new date table for this to work?

Re: Max Date of a previous month based on condition

smpa01 — Wed, 08 Dec 2021 18:24:49 GMT

Anonymous 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

Re: Max Date of a previous month based on condition

VahidDM — Thu, 09 Dec 2021 04:39:59 GMT

Hi Anonymous

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/

Re: Max Date of a previous month based on condition

Anonymous — Thu, 09 Dec 2021 08:37:13 GMT

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.

Re: Max Date of a previous month based on condition

Anonymous — Thu, 09 Dec 2021 08:40:30 GMT

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.

Re: Max Date of a previous month based on condition

VahidDM — Thu, 09 Dec 2021 20:55:50 GMT

Hi Anonymous

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/

Re: Max Date of a previous month based on condition

v-xiaotang — Mon, 13 Dec 2021 10:20:09 GMT

Hi Anonymous

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.

Re: Max Date of a previous month based on condition

Anonymous — Mon, 13 Dec 2021 12:21:48 GMT

Perfect. great job.