SSAS MDX YTD Last Value only

Question

Please could you assist:

fact table a) Actuals

Measure: Weekly Inflow this is the field within the underlying database table [Measures].[Weekly Inflow]

I calculate YTD Inflow using the following

Aggregate ( PeriodsToDate ( [Period End].[Period End].[Year], [Period End].[Period End].CurrentMember ), [Measures].[_Weekly Inflow] )

I want to display a new calculated measure which will show me a single YTD Inflow value only for the very last record where an actual inflow is shown. This must not be repeated as a running total just a single value. Please see the example attached: Screen shot How can i achieve this using a calculated measure? Please could you provide an example?

Date dimension: See screen shot

Please see screen shot 3 after adding the MDX. The values with the pink background should not be shown. Those with a green background need to be presented:Screen Shot 3

Screen shot 4: Additional Measure

Screen shot 5: Screen shot 5

Screen Shot 6

Thank you

MoazRub MoazRub · Accepted Answer · 2019-02-14T16:28:09

So you need to run BottomCount (1 row) on your [Measures].[_Weekly Inflow] to get the last tuple that has a non null value of _Weekly Inflow,this will be a calculated measure .Next, in your YTD measure add a case that checks the current value of the [Period End].[Period End] user hierarchy to the only value of the above measure. I dont have a sample cube at hand but the query structure will be like below.

 with member Measures.[NewColumn]
as 
(bottomcount(
nonempty(
existing
{([Date].[Date].[Date].members)}
,[Measures].[_Weekly Inflow]),
1
).item(0).Name

member 
Measures.[YTD]
as 
case when [Period End].[Period End].currentmember.name=Measures.[NewColumn] then 
Aggregate ( PeriodsToDate ( [Period End].[Period End].[Year], [Period End].[Period End].CurrentMember ), [Measures].[_Weekly Inflow] )
else 
null end

Edit

with member Measures.[NewColumn]
    as 
    (bottomcount(
    filter(
    existing
    {([Date].[Date].[Date].members)}
    ,[Measures].[_Weekly Inflow]>0),
    1
    ).item(0).Name

    member 
    Measures.[YTD]
    as 
    case when [Period End].[Period End].currentmember.name=Measures.[NewColumn] then 
    Aggregate ( PeriodsToDate ( [Period End].[Period End].[Year], [Period End].[Period End].CurrentMember ), [Measures].[_Weekly Inflow] )
when 
[Period End].[Period End].currentmember.name=[Period End].[Period End].defaultmember then Aggregate ( PeriodsToDate ( [Period End].[Period End].[Year], [Period End].[Period End].CurrentMember ), [Measures].[_Weekly Inflow] )
    else 
    null end

Edit

    member 
    Measures.[YTD]
    as 
    case when [Period End].[Period End].currentmember.name=Measures.[NewColumn] then 
    Aggregate ( PeriodsToDate ( [Period End].[Period End].[Year], [Period End].[Period End].CurrentMember ), [Measures].[_Weekly Inflow] )
when 
[Period End].[Period End].currentmember.name="Feburary 2018" then Aggregate ( PeriodsToDate ( [Period End].[Period End].[Year], [Period End].[Period End].CurrentMember ), [Measures].[_Weekly Inflow] )
    else 
    null end

SSAS MDX YTD Last Value only

1 Answers