Rolling sum of days, based on different dates

[Anonymous]

Hi

I'm trying to create a table that calculates the rolling total number of days that someone is booked to do work, based on the last date they are expected to work (which could be in the past or in the future). The number of days may not equate exactly to the start and end dates that someone is booked to work eg they could be booked to work 5 days in a 20 day period. What I do have is the name of the person, start and end dates and the number of days booked to work.

Here is some sample data. 

Name	Start date	End date	Number of days
Person A	01 March 2021	05 March 2021	5
Person A	02 March 2021	20 March 2021	6
Person A	09 March 2021	20 April 2021	7
Person A	01 October 2021	03 October 2021	3
Person A	10 May 2022	11 May 2022	2
Person B	05 November 2022	07 November 2022	3
Person B	01 June 2021	10 June 2021	4
Person B	10 August 2022	14 August 2022	5

So for person A, I want BI to work out that the latest date they are booked to finish working on is 11 May 2022 and calculate that the total number of days in the preceding 12 months (or 365 days) is 5. If they were then booked to work another 5 days between 10th and 14th October 2022 then it would calculate the latest date to be 14 October 2022 and change the calculated number to be 7. I then have person B who is working different dates but I want the total for them to appear in the table as well.

So I want my table to look something like this

Name	Rolling 12 months number of days	Latest date for calculation
Person A	5	11 May 2022
Person B	8	07 November 2022

and when a new date and number of days is added, to look like this

Name	Rolling 12 months number of days	Latest date for calculation
Person A	7	14 October 2022
Person B	8	07 November 2022

Either it's something really simple or too complicated for me to have been able to work out to do so far. I do have a date table that can read the dates.

Help please, wise people!

[v-cgao-msft · Accepted solution]

Hi @Anonymous ,

Please try these measures.

Latest date for calculation = 
CALCULATE ( MAX ( 'Table'[End date] ), 'Table'[Name] = MAX ( 'Table'[Name] ) )

Rolling 12 months number of days = 
CALCULATE (
    SUM ( 'Table'[Number of days] ),
    DATESINPERIOD ( 'Table'[End date], [Latest date for calculation], -365, DAY )
)

Results:

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

[amitchandak]

@Anonymous , Check if this blog can help in this case

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785