Forecasting

[Fiona72]

Hi my goal is to see a count of forecasted work orders for each month over a year by job plan to determine if there are too many work orders in one month to adjust the spread of start dates in source accordingly. The frequency units are in weeks, months & years with various frequencies and start dates. My data is structured like this & I have a calander table.

Job Plan	WO No.	Frequency	Frequncy Units	Start Date
Inspection1	1	2	Weeks	1/3/2024
Inspection2	2	3	Months	15/2/2024
Inspection3	3	1	Year	1/6/2024

I want to visualise it something like this:

JobPlan	Jan	Feb	Mar	etc
Inspection1	5	3	6
Inspection2	2	2	2
Inspection3	8		5
Total	15	5	13

I'm not sure how to tackle it - I think I'd be OK to do the visualisation if I could just get the data extrapoloated! Any help appreciated!!

[amitchandak]

@Fiona72 , Try creating a table like

Work Order Table Expanded =
UNION (
SELECTCOLUMNS (
FILTER (
'Table',
'Table'[Frequency Units] = "Weeks"
),
"Job Plan", 'Table'[Job Plan],
"Date", DATEADD('Table'[Start Date], [Frequency] * 7, DAY)
),
SELECTCOLUMNS (
FILTER (
'Table',
'Table'[Frequency Units] = "Months"
),
"Job Plan", 'Table'[Job Plan],
"Date", DATEADD('Table'[Start Date], [Frequency], MONTH)
),
SELECTCOLUMNS (
FILTER (
'Table',
'Table'[Frequency Units] = "Years"
),
"Job Plan", 'Table'[Job Plan],
"Date", DATEADD('Table'[Start Date], [Frequency], YEAR)
)
)

Then you can try a meausre like

Work Order Count =
COUNTROWS('Work Order Table Expanded')

[Fiona72]

Thank you! I've got it partially working.... it only seems to work if the start date is the first of the month ie it will add the frequency to the date if 1/9/2024 + 1 month = 1/10/2024 but it will not add a month if start date is 5/2/2024?

Also that gives me the next date for each job plan how do I extraploate it out for a year? ie if there's a weekly activity I hope to get 52 forecasted activities?