How to Get YTD numbers

Question & Answer
,
1
board_sub_grouping reporting month1 net_amount
Policy Issuance Medical iTerm 1-Apr-23 100
Policy Issuance Medical iTerm 1-May-23 200
Policy Issuance Medical iTerm 1-May-24 100
Policy Issuance Medical iTerm 1-Apr-24 2000
Policy Issuance Medical iTerm 1-Apr-24 123
This is my data using which I want to achieve YTD sum by selecting single date in the filter.
e.g. Add date parameter on Month1 when I choose 1-May-2024. it should give sum as (Current YTD (1-Apr-24 to 1-May-24) & (Last Year YTD 1-Apr-23 to 1-May-23)
board_sub_grouping reporting Current year YTD Last year YTD
— — — —
Policy Issuance Medical iTerm 2223.00 300

This is my data using which I want to achieve YTD sum by selecting single date in the filter.
e.g. Add date parameter on Month1 when I choose 1-May-2024. it should give some as (Current YTD (1-Apr-24 to 1-May-24) & (Last Year YTD 1-Apr-23 to 1-May-23)

board_sub_grouping reporting Current year YTD Last year YTD
Policy Issuance Medical iTerm 2223.00 300
2

Hi @Lobhas

I don’t think that a YTD is what you want. What you describe is more a rolling sum (including previous year comparison). Please correct me if I am wrong.

Here is what I did for you:
Rolling Sum and Prev. Rolling Sum

I created a date parameter, where you can pick a date.

The first calculation (Sales (Parameter to -1y)) calculates the Sales (please use your meassure here eg. Policy Issuance) for the order date between your selected date and your selected date minus one year.

ifelse(
    {Order Date} <= ${selectedDate}
    AND 
    {Order Date} >= addDateTime(-1, 'YYYY', ${selectedDate})
    ,
    Sales
    ,
    0
)

The second calculation (Sales(selected -2y to -1y)) does the same for the time range selected date -2 years to selected date -1 year.

ifelse(
    {Order Date} <= addDateTime(-1, 'YYYY', ${selectedDate})
    AND 
    {Order Date} >= addDateTime(-2, 'YYYY', ${selectedDate})
    ,
    Sales
    ,
    0
)

Best regards,
Nico

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)

4

Also, this is a good approach.