Calculating Recurring Time Between Start and End Date

[thomasgcoelho]

Hello all!

I'm trying to visualize the total of expected inspections per month.

Example data:

Employee	Number of Inspections Expected	Frequency (days)	Date Start	Date End
Adam	1	10	01/03/2022	31/08/2022
Brigitte	2	10	01/05/2022	31/10/2022

Clarification on the meaning of the data above: Adam needs to do 1 inspection every ten days, starting in 01/03/2022 and ending in 31/08/2022.

The result from the table above would be something like this below, but I'm having trouble making it happen in Power Query:

Table_Expected_per_Month

Employee	january	february	march	april	may	june	july	august	september	october	november	december
Adam	0	0	3	3	3	3	3	3	0	0	0	0
Brigitte	0	0	0	0	6	6	6	6	6	6	0	0

[Ashish_Mathur]

Hi,

So for Adam you want the daes to increment by 10 days starting from March 1, 2022 and go upto August 31, 2022.  How should it be for Brigette - 1 every 5 days?  Please show the expected result in a simple Table format.

[thomasgcoelho]

Hello @Ashish_Mathur !

You're correct about Adam! Here is a clarification in text:

• About Adam: He is expected to do 1 inspection every 10 days starting on March 1, 2022 and ending on August 31, 2022.
• About Brigitte:  She is expected to do 3 inspections every 10 days starting on May 1, 2022 and ending on October 31, 2022.

Result expected in table format:

Employee	january	february	march	april	may	june	july	august	september	october	november	december
Adam	0	0	3	3	3	3	3	3	0	0	0	0
Brigitte	0	0	0	0	6	6	6	6	6	6	0	0

Thank you in advance!

[Ashish_Mathur]

What happens in Feb in which there are less than 30 days?

[thomasgcoelho]

Here is an example of what could happen in February. In this example consider starting date as February 1st, 2022 and 1 inspection in each 10 days:

I'm having trouble creating a table that shows this behavior.

Thank you for the reply.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[thomasgcoelho]

Thank you very much Ashish Mathur!

I was able to use the logic in my real data and achieve the desired result. I'll mark your post as a solution.

[Ashish_Mathur]

You are welcome.