Show future dates with that don't have data in dataset

Question & Answer
, ,
1

I have a table with dates (day level) down the rows. I want to show all the dates to the end of the current month even though there is no data yet in the data set for the current period.

For example the prior year sales for Jan 23, 2024 (jan 23, 2023) is known, but this row does not yet show in my Quicksight analysis because Jan 23 2024 hasn’t occurred yet.

Basically I need to display the dates to the end of the current month, then show the metrics from the same date in the prior year (I have the formulas working, so I don’t need help with those).

The only approach I can think of is to show Jan 23, 2023 and transform a new “adjusted date” out one year (+1 year) then use

periodOverPeriodLastValue(sum({Net Sales}), {Order Date}, YEAR, -1)

Which should go foward one year from 2023.

Any other ideas?

image
image1504×970 130 KB

TIA
Joe

2

Hello @Winojoe, welcome to the QuickSight Community!

You may be able to make this work by replacing your date field with an ifelse statement. Something like this:

ifelse(
  extract('YYYY', {date}) = 2023,
  dateAdd({your_date_column}, 1, 'YYYY'),
  {date}
)

Then it should display the full month of dates, even if the full month for 2024 hasn’t been completed. It may require some extra filters based on if you only want January to show, but this should get you the result you are looking for! Let me know if you have any further questions on this.

1 Like