Forum Discussion
Solved
Comparing Dates between two different tables
I am trying to create a new custom measure that will compare two dates from two different tables. This is how the model has been defined
I need to compare the Agreement[Agreement Activated Date] to the Policy Date which is a calculated column with the following definition:
Policy Date = IF(ISBLANK(Policy[Policy Start Date]), DATE(1900, 1, 1), Policy[Policy Start Date]).
I created this MaxDatemeasure but i dont think this actually compares the activated and start dates by row
Is there a way to compare the dates by row so that it can take the maximum of the two dates on the row and uses that to set as the new maxdate?
You could create a calculated column in the agreement snapshot fact table like
Cancellation Due Date = MAX ( RELATED ( Agreement[AgreementActivatedDate] ), RELATED ( Policy[Policy Date] ) ) + 60
3 Replies
You could create a calculated column in the agreement snapshot fact table like
Cancellation Due Date = MAX ( RELATED ( Agreement[AgreementActivatedDate] ), RELATED ( Policy[Policy Date] ) ) + 60Max( max( Agreement[Agreement Activated Date]), max(Policy[Policy Date]))
That also causes the same issue. I am basically trying to define this Sql logic in Power Bi
date_add(IF(pol.PolicyStartDate > agr.AgreementActivatedDate, pol.PolicyStartDate, agr.AgreementActivatedDate), 60) as CancellationDueDateI tried with IF clauses and SWITCH statements also but i think the issue is more with the model than it is with the dates. If there are any further suggestions, i am willing to try any... thanks
