cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

12-month rolling calculation with two moving time variables

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!

1 ACCEPTED SOLUTION
Community Support

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:

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

2 REPLIES 2
Community Support

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:

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

Frequent Visitor

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.

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors