Create a calculated measure that filters and keeps only one item

Hello,

I do a Rest API import and get a table with the following columns:

  • balance sheet id
  • project code
  • budget
  • balance sheet date

This data are injected in three dimensions:

  • balance sheet (id, budget)
  • project (project code)
  • time (balance sheet date)

The frequency of balance sheets is not constant (at least once a month, but for the same project, it happens that there are several balance sheet per month, either for a more regular follow-up, or to correct an error of a balance sheet previously recorded).

I want to display the evolution of budgets (either cumulative or filtered by project). I am therefore looking to create a calculated measure that filters to keep only one balance sheet (the last one).

For instance :
balance sheet id | project code | budgets | balance sheet date
1 . . . . . . . . . . . . . | PROJ_01 . . . | 100 . . . .| 09/25
2 . . . . . . . . . . . . . | PROJ_01 . . . | 105 . . . .| 09/25

I would like to see a budget of 105 (and not 205) for the month of September.

Could you help me set up this calculated measure ?

thanks in advance

Hello @ThomasLegrand,

Welcome to the eazyBI community!

If multiple members contribute values to the same report context - their values are aggregated unless you identify the member whose value to use.
In your case - the differentiator is the balance sheet ID. I assume it incrementally grows for each new balance sheet version.

The solution is to identify the last balance sheet and take its value.
I suggest cloning the “Balance sheet date” column and importing it as a property for the balance sheet. That will save iterations through the Time dimension while finding the balance sheet date.

The expression for the last applicable balance sheet or “last budget” might then be as follows.

(Order(
  Filter(
   [Balance sheet].[Balance sheet].Members,
--balance sheet for current date   
   DateInPeriod([Balance sheet].CurrentMember.Get('Balance sheet date'),
                [Time].CurrentHierarchyMember)
  AND
--balance sheet for current project
   NOT Isempty([Measures].[Budget])),
--value for sorting
   [Balance sheet].CurrentMember.Key,
--sorting direction
   DESC
--after sorting the balance sheets, take the top one
).Item(0),
 [Measures].[Budget])

However, if multiple projects are selected on the page filter, you might want to sum the values across the selected projects for the current context.

In that case, the expression for the “actual budget” might be as follows.

CASE WHEN
--no or multiple projects selected
[Project].CurrentHierarchyMember.Level.Name = "(All)"
THEN
Sum(
  DescendantsSet([Project].CurrentHierarchyMember, [Project].[Project]),
  ([Measures].[last budget],
   [Project].CurrentMember))
ELSE
[Measures].[last budget]
END

Please check the spelling of measure and dimension names on your instance and update as required.

Regards,
Oskars / support@eazyBI.com

Hello @oskars.laganovskis

Thank you very much for your answer, it meets my needs.
I have adapted the formulas and am manually checking that the values ​​obtained are consistent with what I expected.

I’ll keep you informed

1 Like