When I use “periodOverPeriodDifference(count({Customer}), {Date}, Month, 1)” to calculate the difference of Customer Number between two Month End, I found that If the total days of current month is not same as the total days of last month, the formula cannot work!
And if I use “periodOverPeriodDifference(count({Customer}), {Date}, Days, ???)”, in order to determine the acutal days, I tried like:
Hi @yezhehu,
Is aggregating by month an option for you? Or do you need to see the Last Days of the month as value?
The first calculation you’ve provided should work: periodOverPeriodDifference(count(Customer), {Date}, MONTH, 1)
When you then create the dashboard, just use aggregation by month and the numbers should be ok
If you want you can create a further field, like lastDayOfMonth=addDateTime(-1, 'DD', truncDate('MM', addDateTime(1, 'MM', {Order Date})))
Then you create a table with Date as Group By field, and you select lastDayOfMonth as Value field, with MAX aggregation, and your calculated field for the month over month difference (MonthCalc in my screenshot)
But I actually need to calculate the difference between Last Days’ count({Customer}) value of 2 consecutive months because the value of the Last Days is the aggregated number of total month so that I don’t need to aggregate by month (i.e. your proposal mentioned above)
I can currently select the last day of each month.
If I use the formula like “periodOverPeriodDifference(count({Customer}), {Date}, Month, 1)” and select the date range between Mar 1,2025 - May 31,2025, the red part cannot be calculated correctly.
So I guess that I could change the XXX in “periodOverPeriodDifference(count({Customer}), {Date}, DAY, XXX)” like
when Date = 05/31/2025, XXX=31
when Date = 04/30/2025, XXX=30
when Date = 03/31/2025, XXX=31
when Date = 02/28/2025, XXX=28
…
or could I use another solution to solve this problem?
I tried to use the forumula like “periodOverPeriodDifference(count({Customer}), {Date}, DAY, extract(‘MM’,{Date}))” but it still could not work.
Now you can add this dataset in the same analysis you were using before (if you need also the original dataset to show other info in the same analysis), and if you do the calculation by month, you should obtain what you need.
Yes. I can currently filter the last day of each month, so currently I have the count number of each month end, but when I use “periodOverPeriodDifference(count({Customer}), {Date}, Month, 1)” to calculate the difference between two month, the difference between 2025/3/31 and 2025/4/30 still didn’t work in pivot table calculation in Quicksight.
Or how can I change the parameter in periodOverPeriodDifference in calculation field, I want to conduct like the formula below while it shows Error in Quicksight:
“ifelse(
extract('MM”,{Date}) =5 OR extract('MM",{Date}) =3, periodOverPeriodDifference(count({Customer}), {Date}, Days, 31),
periodOverPeriodDifference(count({Customer}), {Date}, Days, 30)
)";
Hi @yezhehu,
the idea behind a filtered dataset was to simplify things, but you can obtain same thing also with filtering directly in analysis. This is what I was suggesting to do:
We filter the data in advance, so now we just have rows where date fields has a “Last day of the month” value. I’ve used this formula to calculate if it’s last day of the month:
Now I can create a new Date field, where I just keep the “month-year” info, and remove everything else, like:
OnlyMonth = truncDate('MM', {Date})
Now I can create another field, where I calculate the period over period, without worrying about the fact that the last day of the month changes value from month to month:
If you want to, you can then hide the OnlyMonth column, so you’ll have the info and the calculated field will work, but in the table you show the correct date.
