MDX SSAS Calculated measure for last totals + turnovers

• IvanMarkevich

  SSC Enthusiast

  Points: 112

  More actions

  January 10, 2025 at 3:27 pm

  #4513301

  I have a measure with totals for specific dates with the LastNonEmpty aggregation, and a measure with turnovers with the Sum aggregation.

  Help create a measure with totals for each day that would take into account the last known totals + turnovers from the date of the last totals to the current date.

  I tried to get the date of the last totals for each date with this request

  WITH

  MEMBER [Measures].[LastNonEmptyDate] AS

  tail(

  filter(

  periodstodate([date].[calendar].[Date Key],[date].[calendar].currentmember),

  NOT ISEMPTY([Measures].[totals])

  ), 1

  ).item(0).member_caption

  SELECT

  { [Measures].[LastNonEmptyDate] } ON COLUMNS,

  [date].[calendar].[Date Key].MEMBERS ON ROWS

  FROM [test]

  I get null for all members except those where the current date matches the totals date let's say there are totals for 20240101 for 20240101 I get the date 20240101, and for 20240102 it's already null, although the intended value should be 20240101

• Site Owners

  SSC Guru

  Points: 80332

  More actions

  January 11, 2025 at 4:10 pm

  #4513847

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.