Hello everyone,
I have a pivot table that should display the last month spend in one column. It does so perfectly for the current month when filtering for two months, but not for the second month.
Last Month Spend is a calculated field that uses periodOverPeriodLastValue:
periodOverPeriodLastValue(sum({unblended_cost}),{billing_period},MONTH,1)
How can I filter the visual for 2 months without affecting the data?
Thank you very much. Sorry if this was asked before.
Welcome @msedlmair
maybe you will find something here
You have to make sure not to filter out the relevant data.
BR
Hi @msedlmair,
Please see this post for the solution and explanation. You will basically create a new calculated date field, and use that field as the filter for your visual rather than the billing period field directly.
thank you.
Asem
Hi Asem, thanks for the link. It worked like a charm last year but now in Jan 24’, I don’t have “last month spend” in my pivot table.
Fields:
last_month_unblended_spend. (which gives no values)
periodOverPeriodLastValue(sum({unblended_cost}),{Date (Period)},MONTH,1)
Adjusted Order Date:
addDateTime(dateDiff(maxOver({billing_period}, [], PRE_AGG), now()), 'DD', {billing_period})
Date (Period):
ifelse(
${PeriodStarting}='Day',truncDate("DD",{Adjusted Order Date}),
${PeriodStarting}='Month',truncDate("MM",{Adjusted Order Date}),
${PeriodStarting}='Quarter',truncDate("Q",{Adjusted Order Date}),
truncDate("YYYY",{Adjusted Order Date}))
Date Hider:
minOver(min({Date (Period)}), [{Date (Period)}], POST_AGG_FILTER)
I then filter via Date Hider for relative dates, “this month”.
It worked for “previous month” though.