locked
Last Non Empty between specified dates RRS feed

  • Question

  • Hi guys,

    I'm using the following measure to get LNE behavior on some measures:

    Total LNE:=CALCULATE(MAX(Fact[Measure]); LASTNONBLANK(DATESBETWEEN(Dato[Dato]; BLANK(); LASTDATE(Dato[Dato])); CALCULATE(COUNT(Fact[Measure]))))

    If I added an EndDate to the Fact and wanted to make sure that once the LNE hits the EndDate it returns blank. How would I go about doing this?

    Thanks in advance.

    Monday, April 8, 2013 2:41 PM

Answers

  • You probably need the following measure.

    Please, can you describe me the scenario in which you need such a pattern? Are contracts like insurances or something different? It seems an interesting pattern that I'd like to describe better.

    Thanks,

    Marco

    Total LNE :=
    CALCULATE (
        VALUES( Fact[Measure] ),
        LASTNONBLANK (
            DATESBETWEEN (
                Dato[Dato], 
                BLANK(), 
                LASTDATE ( Dato[Dato] )
            ),
            CALCULATE (
                COUNT ( Fact[Measure] ),
                FILTER( VALUES( Fact[EndDate] ), Fact[EndDate] >= Dato[Dato] )
            )
        )
    )


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    • Marked as answer by mortenbpost Wednesday, April 10, 2013 11:55 AM
    Tuesday, April 9, 2013 8:45 PM

All replies

  • You probably need the following measure.

    Please, can you describe me the scenario in which you need such a pattern? Are contracts like insurances or something different? It seems an interesting pattern that I'd like to describe better.

    Thanks,

    Marco

    Total LNE :=
    CALCULATE (
        VALUES( Fact[Measure] ),
        LASTNONBLANK (
            DATESBETWEEN (
                Dato[Dato], 
                BLANK(), 
                LASTDATE ( Dato[Dato] )
            ),
            CALCULATE (
                COUNT ( Fact[Measure] ),
                FILTER( VALUES( Fact[EndDate] ), Fact[EndDate] >= Dato[Dato] )
            )
        )
    )


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    • Marked as answer by mortenbpost Wednesday, April 10, 2013 11:55 AM
    Tuesday, April 9, 2013 8:45 PM
  • Thanks for the quick answer. While your calculation actually solved my problem we ended up implementering a pattern like this: fact_amount -> date <- fact_date_range -> dim_date_range <- the_fact_above_with_the_lne_measure -> dim_misc We then used the cascading many to many calculations you provide in your whitepaper and because we now have the ranges in the dim_date_range it was very easy to get the LNE behavior using the following calculation: Measure LNE:= CALCULATE( MAX(FactWithLNEMeasure[LNEMEASURE]); LASTNONBLANK( DATESBETWEEN(Dato[Dato]; BLANK(); LASTDATE(Dato[Dato])); CALCULATE(COUNT(FactWithLNEMeasure[LNEMEASURE])) ); SUMMARIZE(FactDateRange; DimDateRange[ID_DateRange]) ) But basically i wanted my LNE to end when reaching the enddate. Thanks.
    • Edited by mortenbpost Wednesday, April 10, 2013 11:58 AM spelling errors
    Wednesday, April 10, 2013 11:55 AM
  • I suggest you just two changes.

    1) Use VALUES instead of MAX - if you make some mistake in the filter, you get an error and not a wrong result - better to be sure your formula is working well

    2) You can replace SUMMARIZE... with just the bridge table (see this article: http://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering/)

    Marco

    Measure LNE :=
    CALCULATE (
        VALUES ( FactWithLNEMeasure[LNEMEASURE] );
        LASTNONBLANK (
            DATESBETWEEN ( Dato[Dato]; BLANK(); LASTDATE ( Dato[Dato] ) );
            CALCULATE ( COUNT ( FactWithLNEMeasure[LNEMEASURE] ) )
        );
        FactDateRange
    )


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Wednesday, April 10, 2013 12:08 PM
  • Thanks for the help - read the links and the whitepaper and made the adjustments needed.

    On another note, I have this interesting problem with an accumulated measure when using the chained many to many described in your paper. My measures are defined like this:

    My M2M Measure:=CALCULATE (
        CALCULATE (
            SUM (Fact[Measure]);
            SUMMARIZE (Fact_DateRange; Date[Dim_Date_Code])
        );
        SUMMARIZE(AnotherFact; Dim_DateRange[Dim_DateRange_Code])
    )

    My M2M Measure Accumulated:=
    CALCULATE(
        [My M2M Measure];
        DATESBETWEEN(Date[Date]; BLANK(); LASTDATE(Date[Date]));
        ALL(Date)
    )

    Now if I visualize [My M2M  Measure Accumulated] in a pivot table and at the same time split it on an attribute "Role" found in the "AnotherFact" table which contains the values A and B I get the following result: 


    But what I really wanted is:


    Remember that since the date range concept dictates via AnotherFact that role A is not active before 20120106 - but I still want to accumulate the values from A. I've tried different things but I can't seem to wrap my head around this problem.




    Monday, April 15, 2013 1:42 PM
  • I'm not sure about why you should see "6" for 20120106 in column A - it seems you want to display B in column A when column A has some transactions... can you clarify that?

    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Monday, April 15, 2013 8:10 PM
  • Sure,

    Lets say there are 3 roles: Role A, Role B, and Role C. At any given time you would always either be Role B or C. But in some time periods you would also be Role A in addition to either Role B or C.

    In my example above this person is Role B and also becomes Role A from 20120106 to 20120110. When looking at the measure (in this case engagement), it's not reset just because one suddenly also become Role A, instead it should also include all facts from before becoming A.

    The model is using the chained many-to-many with a date range concept in order to split up a range into discrete values in the date dimension. The model

    contact <-- Fact1 (this fact also contains Role A,B,C and a reference to the date range dim) --> Dim_DateRange <-- Fact_DateRange --> Fact2 (Contains engagement).

    So I want my fact to:

    - Always include all transactions from fact2 (see table above)

    - Honor Fact1 role that dictates when a contact is a certain role using the date range concept.

    I hope this helps explaining what I'm trying to do.

    Tuesday, April 16, 2013 9:27 AM
  • Maybe you are looking for something similar to the following - in case ALL( Fact1 ) removes too many filters, you should consider an ALL on Fact[ID_DateRange] instead.

    M :=
        CALCULATE (
            SUM ( Fact1[Measure] ),
            ALL ( Fact1 ),
            CALCULATETABLE (
                CALCULATETABLE(
                    Contact,
                    Fact1
                ),
                Fact_DateRange
            )
        )


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Tuesday, April 16, 2013 6:12 PM