# 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 ?

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