How to get the latest result so far?

Questions & Answers
1

Hi,
I am trying to get the latest score which gave system so far , just like below ‘latest score’, please help to let me how to define the caculated member with mdx, Thanks a lot

system name, Time, score，latest score
A, Jan 2020,30,30
A, Feb 2020, ,30
A, Mar 2020,10,10
B, Jan 2020,55,55
B, Feb 2020, ,55
B, Mar 2020, ,55
C, Jan 2020, ,
C, Feb 2020, 25,25
C, Mar 2020, , 25
D, Jan 2020, ,
D, Feb 2020, ,
D, Mar 2020, 20,20

Thanks a lot!
Best Regards
Annie Gao

2

Hi @xinranmo,

I believe you have sorted out this use case already =) I just want to recap so other users could apply the solution as well.

To display the las nonempty value for all consecutive members, you can try to create a new calculated measure with the formula below:

  (
    [Measures].[Score],
    Tail(
      Filter(
        [Time].[Month].Members.item(0):
        Tail(
          Descendants([Time].CurrentMember,[Time].[Month])
        ).item(0),
      [Measures].[Score] > 0
    )).Item(0)
  )

It forms a tuple out of the desired measure and the current Time dimension member or the last one that had a value for the desired measure. The Tail() function is responsible for retrieving a sub-set of members from the end of the specified set. The item(0) method gets the first member from this sub-set. Please have a look at our documentation page for more information on the functions used in the calculated measure - https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference.

The report with new calculated measure “Latest Score” might look like this:

image
image600×764 99.3 KB

Best,
Zane and Roberts / support@eazyBI.com

3

Hello @zane.baranovska

I had a similar question elsewhere (Last Record in a Time Period - #3 by Rutwij_Devashrayee) and this solution seems like it would be relevant to that question as well. When I use the above mentioned solution or something like below, I get a sum of the previous day and not the last record of the day. Any idea?

Tail(
  {[Time].[Day].CurrentDateMember.PrevMember,
  [Measures].[Coverage Percentage]},
  1
).Item(0)