Scenario: I have a measure( Sum ), and I need in all situations the result of this sum excluding the last element registered in the fact table respecting the user/day filter. These elements share the same attributes in the other dimensions.
Example: Measure of UserA in fact table in 2019/10/29:
RegisterA: 5
RegisterB: 11
RegisterC: 13
RegisterD: 7
( RegisterD will be not included in the result that the system user will see. Final result: 29 )
Example: Measure of UserB in fact table between 2019/10/26 to 2019/10/29:
RegisterA: 2
RegisterB: 45
RegisterC: 21
RegisterD: 19
( RegisterD will be not included in the result that the system user will see. Final result: 68 )
These elements share the same dimension property, without any difference, but the last fact register inside dimension filters( Day and User ) will never be included in the sum.
What can I do?
Thank You
In your project you need to define each of your measures twice once with "sum" usage and once with "last value" usage. Now define a calculated member that substracts the last value from the sum. Use this calculated member in your queries.