cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ziyabikram96
Helper V
Helper V

Calculate Max of value depend on days having sale.

Dear All 

I have one scenario , table is shown below p1.PNG

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 
1 ACCEPTED SOLUTION
ziyabikram96
Helper V
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 . 

View solution in original post

3 REPLIES 3
ziyabikram96
Helper V
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 . 
lbendlin
Super User
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.

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

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

Helpful resources

Announcements
Microsoft Build 768x460.png

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.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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