cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joetech
Frequent Visitor

Max Date of a previous month based on condition

Hello!

I've got a table with a series of months and values, like this:

CustomerAgreement start dateTypeDateCalculated column I want: latest start date where type = new acquisition and customer = same
12341/6/2020New Aquisition1/1/2021

1/6/2020

 

1234 Renewal1/1/20221/6/2020
12341/6/2022New Acquistion1/1/20231/6/2022
12341/6/2022Renewal1/1/20231/6/2022
44142/7/2022New Acquisition1/1/20232/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?
1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

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

vxiaotang_0-1639390767571.png

 

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.

View solution in original post

7 REPLIES 7
v-xiaotang
Community Support
Community Support

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

vxiaotang_0-1639390767571.png

 

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.

VahidDM
Super User
Super User

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:

VahidDM_0-1639024790397.png

 

 

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/

 

 

joetech
Frequent Visitor

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/

 

 

smpa01
Super User
Super User

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


joetech
Frequent Visitor

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. 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors