Using value day-7 to calculate new value

Hi all, im new to this community, so thanks for your help in advance!

Im trying to figure out how to create a formula/calculated field for forecasting expected passengers on busses. As per screen i have the headcount per shift going forward on daily level (headcount_revised).
From another source i get the past passengers per lane, shift and day (AGT_Ist).
What i want to do is to use past utilization which is AGT_Ist / headcount_revised as per 1. and create another calculated field which should use the utilization_% to calculate the expected passengers W+1 as in 2 based on the headcount W+1.

So basically if the headcount planned is 100 and 50 people were using the bus week-1, means the utilization was at 50%. I want to now use these 50% to forecast next weeks utilization.

So looking for a formular like

Expected_Utilization = utilization Day-7 * headcount_revised

Hello @dggoumah, welcome to the QuickSight Community! There are some options in terms of utilizing forecasting within QuickSight. The only issue is see with what you are trying to accomplish is the ML powered forecasting tool built in QuickSight does not function on a pivot table. It can be implemented on a line graph or in an Insight.

Here is documentation for building out a forecast insight that might work with your data.

Then this is another link to documentation for implementing the forecasting on a line graph which I would say is the easiest way to utilize it!

1 Like

Thx for getting back, let me check

Ive managed to create a calculated field to also work with pivot.
For my usecase i took the utilization from Date - 7 days (or if this is empty day-14 or day-21) to get past utilization for the same weekday in the past (1) via:

periodOverPeriodLastValue({utilization_%}, {ofd_date}, “Day”, 7)

and used that value to calculate expected_utilization (3) by simply multiplying headcount * utilization (2)