Fixed Start Date with Dynamic End Date Filtering in Table Visual

Hello! You can create calculated field that shows the total "Onboarded OL" from a fixed start date to an end date based on the user's date filter selection, you can follow these steps:

Create a Fixed Start Date Parameter

1. In QuickSight, go to the "Analysis" page and click on the "Parameters" option in the top-right corner.
2. Click "Create a parameter" and choose the "Static" parameter type.
3. Set the parameter name (e.g., "FixedStartDate") and data type (Date).
4. Enter the fixed start date you want to use (e.g., January 1, 2020).
5. Click "Create parameter" to save it.
6. Create a Calculated Field for the End Date
7. In the same "Analysis" page, click on the "Add" button in the top-right corner, and select "Add calculated field.
8. Give the calculated field a name (e.g., "EndDate").
9. In the formula editor, use the following expression: if(isNull({DateFilter}), today(), max({DateFilter})) *This expression checks if the user has selected a date range in the filter. If no date range is selected, it uses the current date (today()). If a date range is selected, it takes the maximum date from the filter (max({DateFilter})).

Create the Calculated Field for Total "Onboarded OL"

1. Click "Add calculated field" again and give it a name (e.g., "TotalOnboardedOL"). In the formula editor, use the following expression:

sum(
  if(
    {DateField} >= ${FixedStartDate} && {DateField} <= ${EndDate},
    {OnboardedOL},
    null
  )
)

2. Replace {DateField} with the name of your date field in the dataset, and {OnboardedOL} with the name of the field containing the "Onboarded OL" values. ** Add the Calculated Field to the Visual** In the "Visual" pane, add the "TotalOnboardedOL" calculated field to your visual. Now, when you apply a date filter in QuickSight, the "TotalOnboardedOL" calculated field will show the sum of "Onboarded OL" values from the fixed start date (January 1, 2020, in this example) to the end date based on the user's date filter selection.