Forum Discussion

edgibson's avatar
edgibson
Regular Visitor
7 years ago
Solved

Calculating 30/60/90 Day Interval Help

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!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      edgibson
      Regular Visitor
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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