Calculating 30/60/90 Day Interval Help

[edgibson]

Hello! 

I've searched everywhere and tried all similar questions, but cannot get it to match my data at all. Can someone please help.

I have two data sources. They both have a relationship of account number. 

Data 1 gives me "Assign Date" and "Assigned Amount" These are set dates.

Data 2 gives me "amount" and "Date".  These are transactions. 

I Need to calculate what the balance is from "Assigned Amount" minus total of transactions in 30/60/90 intervals. 

For example: 

If Assign Date is 1/1/2015 with an assigned amount of $1000

and transactions are dated: 1/15/2015 of $100. 2/15/2015 of $500.00. 3/15/2015 of $400.00. 

I would get results of 30 - $900.00, 60 - $400.00, and 90 - $0.

I plan on using this as a slicer with the 30, 60, 90 day "buttons" to display in a card or table the results. 

Does that make sense? and can someone please please help. Thank you so much!

[Anonymous]

Hi @edgibson 
Please provide a sample data that can be copied and paste.

In general,

I would create 3 columns/Measures.

30,60,90.

30 = [Assigned Amount] - CALCULATE(SUM([Amount]), FILTER(DATE RANGE FOR 30))

60 = The same but different date ranges

90 = Same

Thanks!
A

[edgibson]

@Anonymous,

Thank you for the response... i'm trying to get the data thinned out to not have sensitive info, but that's a long task it seems. 

I've tried to enter the column based on what you gave, but the "Date Range"  in the filter is giving errors, the date range needs to pull from "assigned date" + 30. where assigned date is years worth of different dates. 

[Anonymous]

Hey @edgibson 

That is fine.

Create something like

30 = 
CALCULATE(...
FILTER(Table, [Date] <= 'Table'[Assigned Date] + 30)
...
)

That should give you the date range from [Assigned Date] to [Assigned Date] + 30

Cheers!
A

[edgibson]

@Anonymous I think I'm getting somewhat close.... thank you for the continued assistance...

30 = '070719ahareport'[ Assigned Amount ] - CALCULATE(SUM('Sheet1 (2)'[amount], FILTER('Sheet1 (2)'[amount] + 30)))

[Anonymous]

@edgibson 

I believe you need to put the dates in the filter, not the amount. The amount is in the SUM function.

Cheers!
A

[edgibson]

@Anonymousthanks for your help. I think i'm ready to give up. 

30 = '070719ahareport'[ Assigned Amount ] - CALCULATE(SUM('Sheet1 (2)'[amount], FILTER('Sheet1 (2)','Sheet1 (2)'[Date] <= '0707ahareport'[ assign date] + 30)))

tells me too many arguments were passed to the SUM function. The maximum count for the function is 1. 

[Anonymous · Accepted solution]

@edgibson 
Yes.
SUM shold have 1 argument. Also try putting the last argument in brackets.

30 =
'070719ahareport'[ Assigned Amount ]
    - CALCULATE (
        SUM ( 'Sheet1 (2)'[amount] ),
        FILTER (
            'Sheet1 (2)',
            'Sheet1 (2)'[Date] <= ( '0707ahareport'[ assign date] + 30 )
        )
    )

Thanks!
A

[edgibson]

Thank you @Anonymous ! I just need to maneuver the calculation a bit - but that gives me the basis of what I needed. Thank you for the persistence and help! 

Cheers! 

Ed

[Anonymous]

Good Luck.
Next time try to provide sample data, it will be easier to assist.

Follow the same logic for the 60 and 90 columns. 

Thnaks!
A

[Anonymous]

Hey @edgibson 

That is fine.

Create something like

30 = 
CALCULATE(...
FILTER(Table, [Date] <= 'Table'[Assigned Date] + 30)
...
)

That should give you the date range from [Assigned Date] to [Assigned Date] + 30