Forum Discussion

SebL's avatar
SebL
Frequent Visitor
4 years ago

12-month rolling calculation with another moving date variable (Second Attempt - More Description)

Hi Folks, 

Using September 2021 RS desktop. 

 

I'm trying to build a measure for 12-month rolling costs, which seemed to work out.

 

CostR12 = 
Var SelectedMaxDate = MAX ('Claims Costs Transactions'[DatePaidOrAuthorized])
Var MinDate = 
        CALCULATE ( 
            MIN ( 'Date'[Date] ),
            FILTER (
                  ALL ( 'Date' ),
                  DATEADD ( 
                        'Date'[Date],
                        1,
                         YEAR
                  ) > SelectedMaxDate
            )
      )
Return
CALCULATE (
      SUM ( 'Claims Costs Transactions'[Amount] ),
      ALL ( 'Date' ),
      'Date'[Date] <= SelectedMaxDate,
      'Date'[Date] >= MinDate
)

 

 

 

 

However, I also required the measure to be categorized by another measure which also has a date variable. IF the sum of a field in another table is >=180, the costs should be labeled "Yes". I built a table measure to determine if a 'ReferenceNu' has accumulated enough NbDays by the end of a period. 

 

Claim Days Paid = 
Var SelectedMaxDateDaysPaid = MAX ('Date'[Date])
Return
CALCULATETABLE(
    SUMMARIZE(
        'Days Lost Transactions',
        'Days Lost Transactions'[ReferenceNu],
        "Total Days Paid", SUM('Days Lost Transactions'[NbDays]),
        "180 Day Claim", IF(SUM('Days Lost Transactions'[NbDays])>=180,"Yes","No")
    ),
    FILTER(
        'Days Lost Transactions',
        'Days Lost Transactions'[DatePaidOrAuthorized] <= SelectedMaxDateDaysPaid
    ),
    FILTER(
    'Days Lost Transactions',
    'Days Lost Transactions'[PaymentGroupCD] = "COMP"
))

 

 

It isn't working though, and applies the latest result for all periods. For example, for the referencenumber in the image above we should be seeing the result in the table below. As that reference number hasn't acumulated 180 days until Q2 2022.

Year QuarterReferenceNu180 Day Claim
Q4 202120212866No
Q1 202220212866No
Q2 202220212866Yes

 

Here is my model:

 

 

What's the best approach? 

Thanks for taking the time to read through this. 

 

5 Replies