How to Get YTD numbers

Question & Answer
,
3

Hi @Lobhas, to achieve the Year-to-Date (YTD) sum calculation using a single date filter, you can try this approach – or if it’s too many steps, I would recommend handling the logic in pre-processing at the dataset or custom SQL or the above solution from Nico.

  1. Create a Date Parameter:
  • Open your analysis in QuickSight.
  • Create a new parameter by going to the “Parameters” section and clicking “Create one”. Name it SelectedDate, set its data type to Date, and choose a default value.
  1. Create Calculated Fields for Current Year and Last Year:

Current Year YTD Calculation:

Pseudocode (Syntax may vary)

ifelse(
    {Month1} <= {SelectedDate} 
    and {Month1} >= dateDiff(truncDate('YYYY', {SelectedDate}), 'YYYY', -1), 
    {net_amount}, 
    0
)

Name this calculated field CurrentYearYTD.

Last Year YTD Calculation:

Pseudocode (Syntax may vary)

ifelse(
    {Month1} <= dateDiff({SelectedDate}, 'YYYY', -1) 
    and {Month1} >= dateDiff(truncDate('YYYY', {SelectedDate}), 'YYYY', -2), 
    {net_amount}, 
    0
)

Name this calculated field LastYearYTD.

  1. Aggregate the Calculated Fields:

Sum of Current Year YTD:
Pseudocode (Syntax may vary)

sum({CurrentYearYTD})

Name this aggregated field SumCurrentYearYTD.

Sum of Last Year YTD:
Pseudocode (Syntax may vary)

sum({LastYearYTD})

Name this aggregated field SumLastYearYTD.

  1. Create a Table Visual:
  • Add a new table visual to your analysis.
  • Add board_sub_grouping and reporting to the rows of the table.
  • Add SumCurrentYearYTD and SumLastYearYTD to the values of the table.
  1. Add the Parameter Control to the Analysis:
  • Go to the “Parameters” section, click on the SelectedDate parameter, and choose “Add control”.
  • Set the control type to a date picker so you can select a specific date.

Here’s the step-by-step implementation:

  1. Create SelectedDate Parameter:
  • Name: SelectedDate
  • Data type: Date
  • Default value: (choose any date, e.g., 1-May-2024)
  1. Create CurrentYearYTD Calculated Field:

Pseudocode (Syntax may vary)

ifelse(
    {Month1} <= {SelectedDate} 
    and {Month1} >= dateDiff(truncDate('YYYY', {SelectedDate}), 'YYYY', -1), 
    {net_amount}, 
    0
)
  1. Create LastYearYTD Calculated Field:

Pseudocode (Syntax may vary)

ifelse(
    {Month1} <= dateDiff({SelectedDate}, 'YYYY', -1) 
    and {Month1} >= dateDiff(truncDate('YYYY', {SelectedDate}), 'YYYY', -2), 
    {net_amount}, 
    0
)
  1. Create Aggregated Fields:
  • SumCurrentYearYTD:

Pseudocode (Syntax may vary)

sum({CurrentYearYTD})
  • SumLastYearYTD:

Pseudocode (Syntax may vary)

sum({LastYearYTD})
  1. Add the Table Visual:
  • Rows: board_sub_grouping, reporting
  • Values: SumCurrentYearYTD, SumLastYearYTD
  1. Add the Parameter Control:
  • Control type: Date picker
  • Parameter: SelectedDate

This approach can allow you to dynamically filter and calculate the YTD sums for both the current and last year based on the selected date parameter in QuickSight.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

Also, check out the list of QuickSight Functions and this forum:

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)