12-month rolling calculation with two moving time variables

[SebL]

Hi Folks, 

I am using the September 2021 Report Server version of Desktop. 

I need to design a measure for a line graph that displays the 12-month rolling costs every quarter (and month in a seperate chart), but inclusion in the costs are also dependant on whether they meet the criteria in another table. 

For any period (quarter or month), I need the 12-month rolling costs (amount in Costs Table) IF the total dayspaid (Sum of NbDays in Days Paid Table) has reached >=180 by that point.   

Costs Table

AccountTransactionID	ReferenceNumber	Amount	DatePaidorAuthorized
1234567	20201234	$5000.00	1/1/2022
1234568	20201234	$45.67	4/3/2022
1234569	20201234	$235.62	5/6/2022
1234570	20221567	$127.45	1/12/2021
1234571	20221567	1	5/12/2021

Days Paid Table

AccountTransactionID	ReferenceNumber	NbDays	DatePaidorAuthorized
1234567	20201234	4	1/1/2022
1234568	20201234	12	4/3/2022
1234569	20201234	48	5/6/2022
1234570	20221567	2	1/12/2021
1234571	20221567	1	5/12/2021

Hope this makes sense, let me know if you need any clarification. 

Thanks!

[v-cgao-msft · Accepted solution]

Hi @SebL ,

You need a calendar table and use the calendar table to create relationships with 'Costs Table' and 'Days Paid Table'.

Calendar table:

Relationships:

Then please create these measures:

Sum of NbDays = 
IF (
    ISINSCOPE ( 'Calendar'[Quarter] ) || ISINSCOPE ( 'Calendar'[Month] ),
    CALCULATE (
        SUM ( 'DaysPaidTable'[NbDays] ),
        'DaysPaidTable'[ReferenceNumber] = MAX ( 'DaysPaidTable'[ReferenceNumber] )
    )
)

12-month rolling costs = 
VAR _start = MAX('Calendar'[Year])*100+MIN('Calendar'[MonthNum])
VAR _end = _start+100
VAR _result =
IF (
    [Sum of NbDays] >= 180,
    CALCULATE (
        SUM ( 'CostTable'[Amount] ),
        FILTER (
            ALL('Calendar'),
            'Calendar'[Year-Month]>=_start&&'Calendar'[Year-Month]<=_end          
        )
    )
)
RETURN
_result

Test result:

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[SebL]

I want to add that the ReferenceNumber is the field used to group the NbDays sums in the Days Paid Table and determine inclusion in the Costs Table.