cancel
Showing results for
Did you mean:
Helper V

## Calculate Max of value depend on days having sale.

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

1 ACCEPTED SOLUTION
Helper V

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 .
3 REPLIES 3
Helper V

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 .
Super User

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.

Helper V

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

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Check it out!

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

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors