Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
SebL
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

AccountTransactionIDReferenceNumberAmountDatePaidorAuthorized
123456720201234$5000.001/1/2022
123456820201234$45.674/3/2022
123456920201234$235.625/6/2022
123457020221567$127.451/12/2021
12345712022156715/12/2021

 

Days Paid Table

AccountTransactionIDReferenceNumberNbDaysDatePaidorAuthorized
12345672020123441/1/2022
123456820201234124/3/2022
123456920201234485/6/2022
12345702022156721/12/2021
12345712022156715/12/2021

 

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

Thanks!

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
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:

vcgaomsft_0-1659327021824.png

Relationships:

vcgaomsft_1-1659327047606.png

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:

vcgaomsft_2-1659327244727.png

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

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
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:

vcgaomsft_0-1659327021824.png

Relationships:

vcgaomsft_1-1659327047606.png

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:

vcgaomsft_2-1659327244727.png

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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