none
Semi-additive - earlier context RRS feed

  • Question

  • My SSAS knowledge is pretty limited so apologies up front for any incorrect use of terminology!

    I have a semi-additive measure (like below) which works perfectly fine in its current context (e.g. if summarised to a month, I'm getting the total for the last populated date)

    Closing balance:=CALCULATE(
    SUM(SpeciesBalance[Balance]),
    LASTNONBLANK(
    Calendar[Date],
    COUNTROWS(RELATEDTABLE('SpeciesBalance'))
    )
    )
    

    However, what I want to now do is if I have broken my Date to a daily level and included another measure for which this measure doesn't have any data, I want to use the value from the most recent day prior to the current context

    So if I have something like the below

    Date          Measure
    1/1           5
    2/1            
    3/1 
    4/1           10
    5/1 
    6/1
    7/1           8
    8/1
    9/1
    I want to display
    Date          Measure
    1/1           5
    2/1           5
    3/1           5
    4/1           10
    5/1           10
    6/1           10
    7/1           8
    8/1           8
    9/1           8

    I'm currently suspecting I need to do one of those SUMX style measures to determine the most recent date and use that for my result but I have no idea where to start or what to look at...

    Thanks

    Thursday, January 18, 2018 11:02 PM

All replies

  • If it is DAX, there's quite probably something about it at sqlbi

    Expect me to help you solve your problems, not to solve your problems for you.

    Friday, January 19, 2018 8:53 AM
  • If it is DAX, there's quite probably something about it at sqlbi

    Expect me to help you solve your problems, not to solve your problems for you.

    Yeah, I had looked there, the use of the CALCULATEDTABLE looked half like what I was after, however, it all always seems to apply to the current context, I need to figure out how to take it out of the current context.

    It may well be in the LASTNONBLANK that I have to make the adjustment, if I take that bit in to a different measure, I'm only getting values on the dates where we actually have values

    :=LASTNONBLANK ( 'Date'[Date], CALCULATE ( SUM ( [Count] ) , all('Date'[Date])))

    Sunday, January 21, 2018 7:12 PM
  • In fact, this sounds exactly what I'm after

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7012634f-1ff7-4073-b72c-899696ced12e/dax-last-non-empty-per-date-most-current-inventory?forum=sqlanalysisservices

    but unfortunately, not working for me

    I have tried to pull it back to just "last entry, ignore subcat" but still not populating dates where no amount exists

    s:=    CALCULATE (
            SUM ( 'Stock'[Count] ),
            CALCULATETABLE (
                LASTNONBLANK (
                    'Date'[Calendar Date],
                    CALCULATE ( COUNTROWS ( 'Stock' ) )
                ),
                DATESBETWEEN (
                    'Date'[Calendar Date],
                    BLANK (),
                    MAX ('Date'[Calendar Date])
                )
            )
        )

    Sunday, January 21, 2018 7:54 PM
  • Can't say it's the RIGHT solution but a post on that previous link gave me the answer... adding ALL to both the CALCULATE and CALCULATETABLE got me what I was after (doesn't sound efficient but doesn't seem to be performing too badly for my data set)

    s:=    CALCULATE (
            SUM
    ( 'Stock'[Count] ),
            CALCULATETABLE
    (
                LASTNONBLANK
    (
                   
    'Date'[Calendar Date], CALCULATE ( COUNTROWS ( 'Stock' ) )
               
    ),
                DATESBETWEEN
    (
                   
    'Date'[Calendar Date],
                    BLANK
    (),
                    MAX
    ('Date'[Calendar Date])
               
    ), ALL('Date')
           
    ), ALL('Date')
       
    )

    Sunday, January 21, 2018 10:58 PM
  • Hi RyanAB,

    Thanks for your question.

    I am glad to know that you have found the solution by yourself. Please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing similar issues. Thanks for your contribution.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, January 22, 2018 12:56 AM
    Moderator