My problem involves creating a calculation in QuickSight to replicate the Past ARR value of the latest expired contract across all rows for each account, while filtering out future contracts. The challenge is that QuickSight does not allow nested aggregation functions or direct comparisons between aggregated and row-level fields, which leads to errors when trying to match the past contract’s date and its corresponding TCV.
I tried to do (Latest_Date_Expired_Contract)
maxOver(maxIf({Last Date of Contract}, {Last Date of Contract} < now()), [{Account Name}])
To replicate in each rows the Latest Date of Expired Contract.
Then I tried different approaches using firstValue, MaxOver based on the filter above to get the Past ARR value. For example:
firstValue(
ifelse({Last Date of Contract} = {Latest_Date_Expired_Contract}, TCV, NULL),
[{Last Date of Contract} DESC],
[{Account Name}]
But, my aim here is that:
I have Accounts Opened by Campaigns.
And I want to replicate at the Account level, based on the campaigns dates, if the Campaign Contract was expired, the value that corresponds to its Latest Expired Campaign.