SUMMARIZE WITH FILTER FOR FUTURE DATES

[Anonymous]

Hi @Ashish_Mathur @amitchandak @Greg_Deckler and others

thanks in advance

I have a complex calculation on a line fact table, am trying to filter the line item of LetterDate or LastDate by line key based on the future dates.

I have created the virtual table but its looking first line key ( first key based on the loan key) but its error.

Calculation which i have used is:

Loan Virtal Table = SUMMARIZE(

'Loan',

'Loan'[LoanKey] ,

"LineKey",

CALCULATE (

FIRSTNONBLANK (

'Loan'[LineKey],

'Loan'[LineKey]

),

FILTER (

'Loan',

'Loan'[LetterDate] >= TODAY ()

||'Loan'[LastDate] >= TODAY ()

)))

LetterDate = LOOKUPVALUE(Loan[LetterDate],Loan[LineKey],'Loan Virtal Table'[LineKey])

LastDate = LOOKUPVALUE(Loan[LastDate],Loan[LineKey],'Loan Virtal Table'[LineKey])

Sample data https://1drv.ms/f/s!AqovrnTqKKwGhAreglb3PUE0bk1D 

file name sample 1.pbx

Regards,

chetan 

[V-pazhen-msft · Accepted solution]

@Anonymous 
You can create a calculated column that matches the your condition in the fact table before summarize a table.

first Linekey = CALCULATE(FIRSTNONBLANK(Loan[LineKey],1),FILTER(Loan,'loan'[LetterDate] >= TODAY ()
                ||'Loan'[LastDate] >= TODAY ()),FILTER(Loan,Loan[LoanKey]=EARLIER(Loan[LoanKey])))

Pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EVj80B6L7KhEsQ-ICJ0qapQBBUJgcstxmReJXJP_9abAyQ?e=YtHa8a

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

I am not clear with your question.  Please explain the business context, share a simple dataset and show the expected result.

[Anonymous]

According to your post what I understood is

Ex: "A" is a landlord and "B" is Tentant and like wise "A" is having multiple Tentants ("C/D/E/F/G/H")
Tenant "B" is leasing home from "A" and the lease has StartDate and EndDate.

Landlord "A" wants to be intimated based on the nearing date for tenant "A"
as he might increase the Lease amount and Lease Agreement.

You dataset has the "Letterdate" as "lease startdate" and "LastDate" as "Lease EndDate".

I was thinking of workaround by using If Condition

NotifyLandlord=if(data[LastDate]>= TODAY(),1,0)

if the value returns as 1 then it is indication that Landlord "A" has to follow up with "B" on further process.

[Anonymous]

@Anonymous  your current on the but the if the condition was not working because it gives 1 for all the customer id

[amitchandak]

@Anonymous , expected output is not very clear. Can you provide that

[Anonymous]

- Need Future Dates linekey for the Last Date, Letter Date which is >= Today and its only recent upcoming linekey 

Example

When the Last Date is 4/1/2019 and  Letter Date is 5/1/2021  this is True then I need to consider the linekey 

When the Last Date is 5/1/2021 and  Letter Date is 4/1/2019 this is True then I need to consider the linekey 

When the Last Date is 4/1/2018 and  Letter Date is 4/1/2019 this is False then ignore the linekey