Hi All,
I'm looking to create metrics based on transactional records of total spend, total number of PO's etc. We're looking to measure how trends increase from month to month since our program is ramping up, it will help give us insight into the upcoming demand.
Currently, if I hard code the dates the formulas are working as intended. I'm looking to convert these formulas over to variables so they always represent the trailing 1 month, 2 month etc. I have setup variables that represent the dates I want, yet when I try to incorporate them into set analysis the formula does not work. Any assistance would be greatly appreciated!
Formulas that work
__________________________________
April Spend- aggr(Sum({<[Reporting Month End Date] ={'4/30/2021'}>} Distinct EXT_PRICE_PO), [PO Key])
May Spend- aggr(Sum({<[Reporting Month End Date] ={'5/31/2021'}>} Distinct EXT_PRICE_PO), [PO Key])
% Difference-
aggr(Sum({<[Reporting Month End Date] ={'5/31/2021'}>} Distinct EXT_PRICE_PO), [PO Key])
/
aggr(Sum({<[Reporting Month End Date] ={'4/30/2021'}>} Distinct EXT_PRICE_PO), [PO Key])
$(vStart_Date) = addmonths(Max([Reporting Month End Date]),-1)
$(vEnd_Date) = addmonths(Max([Reporting Month End Date])
What doesn't work - Incorporating the variable into the formula I've tried a few different versions of syntax to no avail, example is:
aggr(Sum({<[Reporting Month End Date] ={"$(vStart_Date)"}>} Distinct EXT_PRICE_PO), [PO Key])
Any help would be greatly appreciated!
