We have 2 custom fields that we want to report on a single measure:
$ Most Likely Value and Company.
There is a measure for $ Most Like Vale closed and we want to get the formula for “$ Most Like Vale closed” and the specific previous value of the custom field company which is “NEW PROSPECT” before it is changed to the correct company name.
We have tried this formula but it does not seem to work. Please help.
(
[Measures].[$ Most Likely Value (k) closed],
[Transition Field].[Company],
[Company].[NEW PROSPECT]
)
Hi @jeno ,
You need to use an issue history measure “Transitions from issues count” to see issues that have transitioned from value “NEW PROSPECT”. And then sum those issues by “$ Most Likely Value (k) closed”. Additionally, I added two other filters - by issue closed date and that company’s current value is not “NEW PROSPECT” :
Sum(
--filter issues
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
--issues are closed in period, if time is used in report, the issues will be filtered by closing date
DateInPeriod(
[Measures].[Issue closed date],
[Time].CurrentHierarchyMember
)
AND
--issue Company IS NOT "NEW PROSPECT"
[Measures].[Issue Company] <> "NEW PROSPECT"
AND
--issue Company value WAS L
([Company].[NEW PROSPECT],
[Measures].[Transitions from issues count],
[Transition Field].[Company],
[Time].CurrentHierarchy.DefaultMember)>0
),
-- sum by measure "$ Most Likely Value (k) closed"
[Measures].[$ Most Likely Value (k) closed]
)