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

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

  • 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

     

    Cheers!
    A

    • edgibson's avatar
      edgibson
      Regular Visitor
      7 years ago

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

        edgibson 

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

        Cheers!
        A