Calculate Max of value depend on days having sale.

[ziyabikram96]

Dear All 

I have one scenario , table is shown below 

In this table I want to calculate the Maximum of bank balance of previos 8 days but when Document type is SI  , which will be on documane date  31/12/2022, and bank balance is 2341, i tried this dax formula but its only calculate previous minus 8 days whcih is 6-01-2022, but i need only the previous dates whcih have bank balance. then I want to take amount with SR from previos 8 day add them and subtract them from amount i got form above.

for example = 2341 - (1850+1500) Note == (1850 and 1500 is value with SR whcih is after 31/12/2022) 

Amount due 7 days = VAR previousweek =MAX(CUSTOMER_TRANSACTIONS[DOCUMENT_DATE]) -8

VAR _tbl = CALCULATETABLE(ADDCOLUMNS(SUMMARIZE(CUSTOMER_TRANSACTIONS,CUSTOMER_TRANSACTIONS[DOCUMENT_DATE],CUSTOMER_TRANSACTIONS[Bank Balance],CUSTOMER_TRANSACTIONS[Document Type]),"@average", previousweek2),ALLSELECTED(CUSTOMER_TRANSACTIONS[DOCUMENT_DATE]), ALLSELECTED(CUSTOMER_TRANSACTIONS[Document Type]))

VAR lastmaxdate =MAXX(_tbl, [@average])

VAR amount = CALCULATE(MAX(CUSTOMER_TRANSACTIONS[Bank Balance]),FILTER(_tbl,CUSTOMER_TRANSACTIONS[DOCUMENT_DATE]= lastmaxdate && CUSTOMER_TRANSACTIONS[Document Type]=="SI"))

VAR SR_PAYMENT = CALCULATE ( SUM ( CUSTOMER_TRANSACTIONS[Amount] ),

FILTER (

CUSTOMER_TRANSACTIONS,

CUSTOMER_TRANSACTIONS[Document Type] == "SR"

&&

CUSTOMER_TRANSACTIONS[DOCUMENT_DATE] > lastmaxdate))

VAR Sub = amount - SR_PAYMENT

Return Sub

Thanks in advance 

[ziyabikram96 · Accepted solution]

I fond the solution I just made this Measure take filter 

Amount Due 7 days =

VAR Filter_exp_SII = SUMMARIZE( FILTER(CUSTOMER_TRANSACTIONS, CUSTOMER_TRANSACTIONS[DOCUMENT_DATE] < today() -8 && CUSTOMER_TRANSACTIONS[Document Type] =="SI"),

CUSTOMER_TRANSACTIONS[DOCUMENT_DATE],CUSTOMER_TRANSACTIONS[Bank Balance],CUSTOMER_TRANSACTIONS[Document Type])

VAR Filter_exp_SR = SUMMARIZE( FILTER(CUSTOMER_TRANSACTIONS, CUSTOMER_TRANSACTIONS[DOCUMENT_DATE] < today() && CUSTOMER_TRANSACTIONS[Document Type] =="SR"),

CUSTOMER_TRANSACTIONS[DOCUMENT_DATE],CUSTOMER_TRANSACTIONS[Bank Balance],CUSTOMER_TRANSACTIONS[Document Type],CUSTOMER_TRANSACTIONS[Amount])

VAR Max_ = CALCULATE( MAXX(Filter_exp_SII,CUSTOMER_TRANSACTIONS[DOCUMENT_DATE]))

VAR amount = CALCULATE(MAX(CUSTOMER_TRANSACTIONS[Bank Balance]),FILTER(Filter_exp_SII,CUSTOMER_TRANSACTIONS[DOCUMENT_DATE]= Max_ ))

VAR SR_= CALCULATE( MAXX(Filter_exp_SR,CUSTOMER_TRANSACTIONS[DOCUMENT_DATE]))

VAR amount_SR = CALCULATE(sum(CUSTOMER_TRANSACTIONS[Amount]),FILTER(Filter_exp_SR,CUSTOMER_TRANSACTIONS[DOCUMENT_DATE]> Max_))

VAR Sub = Amount - amount_SR

Return Sub

I took filter table first and then cumpute it . 

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[ziyabikram96]

Hi thanks  for data please find the attach link and for out put check the image. 

https://drive.google.com/file/d/1Y0Kf8nX9swUrjcga7dNOG_pCOWuxruQL/view?usp=sharing