sql server - MDX SSAS Calculated measure for last totals + turnovers - Stack Overflow

admin管理员组

文章数量:1123227

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

本文标签： sql serverMDX SSAS Calculated measure for last totalsturnoversStack Overflow