Pivot table - compute daily costs

Question & Answer
, ,
1

I have a pivot table with monthly costs. I want to instead calculate daily costs for each item. So for example, Feb-25 row null is $28,701 so the daily cost is $28,701.00/28 = $1025.04. For Mar-25 row null is $28,777.44 so the daily cost is $28,777.44/31 = $928.30.

How do I do this?

image
image1129×600 54.1 KB

1 Like
2

Hello @John_J, welcome to the QuickSight community!

In order to accomplish this, you would need a calculated field to return the cost_amortized if the row field you are using is not NULL, otherwise return the cost amortized divided by the number of days in the month. It would look something like this:

ifelse(isNull({Row Field}), 
{Cost_Amortized}/ extract("DD", addDateTime(-1, "DD", addDateTime(1, "MM", truncDate("MM", {Date})))),
{Cost_Amortized})

I know this part of the function looks a little complicated:
extract("DD", addDateTime(-1, "DD", addDateTime(1, "MM", truncDate("MM", {Date}))))

Basically, I am trying to dynamically divide by the number of days in each month. I convert the date to a month date, make it equal to the first date of the following month, then I subtract 1 day to extract the number of days in the month.

Let me know if this helps!

3

Heeey that worked! Thank you so much! I wanted the null row too, so here’s what I ended up with -

{Cost_Amortized}/ extract("DD", addDateTime(-1, "DD", addDateTime(1, "MM", truncDate("MM", {billing_period}))))

1 Like