none
DAX: Last Non Empty per date (Most Current Inventory) RRS feed

  • Question

  • Here is a little context.  I have product subcategory as row label.  I have a daily snapshot of inventory and using count of inventory as the measure.  The measure I want to be sure that I return the latest dated inventory when I don't specify a date as the column label.  But if a date is specified then I want to be sure to show that dates inventory in which the cell relates.  Inventory from day to day can go to 0.  So not everyday a subcategory has inventory. I have tried using LastNonEmpty to return the last date.  But what happens is one subcategory is from today and the next subcategory is from the previous day that did have inventory for that date.  I have been able to create a measure that returns the max date for which the column pertains.  Here is that formula.  CALCULATE(MAX([Business Date]), ALLEXCEPT('Inventory', 'Date')) 

    What I cannot seem to figure out is how to put that into the filter context for the inventory count so I return inventory which only pertains to that date.  Any and all help is appreciated!

    • Edited by DataNerd Tuesday, February 4, 2014 3:22 PM edit title
    Tuesday, February 4, 2014 3:13 PM

All replies

  • Hi DataNerd,

    Thank you for your question. 

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, February 5, 2014 6:46 AM
    Moderator
  • We need to study your project in order to understand your business logic and help you with your enquiries.

    From a support perspective this is really beyond what we can do here in the forums. If you cannot determine your answer here or on your own, consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone. Thanks.

    Wednesday, February 19, 2014 1:45 AM
  • If every subcategory can have a different date, you should use this technique:

    Inventory :=
    SUMX (
        VALUES ( Product[Subcategory] ),
        CALCULATE (
            SUM ( Inventory[Quantity] ),
            LASTNONBLANK (
                'Date'[Date],
                CALCULATE ( COUNTROWS ( Inventory ) )
            )
        )
    )

    You might also use this approach (but I prefer the former because it is potentially faster in the LASTNONBLANK iteration):

    InventoryInternal := SUM ( Inventory[Quantity] )
    Inventory :=
    SUMX (
        VALUES ( Product[Subcategory] ),
        CALCULATE (
            [InventoryInternal],
            LASTNONBLANK (
                'Date'[Date],
                [InventoryInternal]
            )
        )
    )


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

    Wednesday, February 19, 2014 4:27 PM
  • Hi Marco,

    Thought this would be the solution for the same last non empty question I asked you on your site, but it still doesn't work.  Are you sure that your example gets the LAST value for each Product subcategory and not just the LAST value for the Product subcategories for the date context of the pivot?

    For example, when I use your approach to aggregate the last value for each account as per the following DAX, I get the same result as SUM(EndingRemainingPrincBalance) at the day level. And not sure what is happening at the higher levels, i.e. week, month, quarter, year, but it looks like it's getting all of the last date amounts for that period ONLY if the account had a transaction in that period.  I need the last transaction prior to the context period, not just limited to that period.

    Russo:=SUMX (
        VALUES ( DimAccount[CPAccountID] ),
        CALCULATE (
            SUM ( vwCPAccountRevenueRecognition[EndingRemainingPrincBalance] ),
            LASTNONBLANK (
                DimDate[FullDate],
                CALCULATE ( COUNTROWS ( vwCPAccountRevenueRecognition ) )
            )
        )
    )





    • Edited by blezin Sunday, May 10, 2015 4:35 PM tmi
    Tuesday, April 28, 2015 4:38 PM
  • Hello again,

    To illustrate my point further, I modified your DAX to count the unique accounts, aka products, used to aggregate the last inventory value.  It demonstrates that it's only using the accounts that have records on the pivot date context, NOT all accounts that have transactions prior to that date.  Hence, the total inventory value excludes accounts that don't have values in the context period.

    =SUMX (
        VALUES ( DimAccount[CPAccountID] ),
        CALCULATE (
           DistinctCount ( vwCPAccountRevenueRecognition[cpaccountid] ),
            LASTNONBLANK (
                DimDate[FullDate],
                CALCULATE ( COUNTROWS ( vwCPAccountRevenueRecognition ) )
            )
        )
    )



    • Edited by blezin Sunday, May 10, 2015 4:34 PM tmi
    Wednesday, April 29, 2015 12:34 AM
  • You're right, if you want to consider previous dates you have to extend the range of dates considered by LASTNONBLANK using the following syntax (see parts I added in bold):

    Russo :=
    SUMX (
        VALUES ( DimAccount[CPAccountID] ),
        CALCULATE (
            SUM ( vwCPAccountRevenueRecognition[EndingRemainingPrincBalance] ),
            CALCULATETABLE (
                LASTNONBLANK (
                    DimDate[FullDate],
                    CALCULATE ( COUNTROWS ( vwCPAccountRevenueRecognition ) )
                ),
                DATESBETWEEN (
                    DimDate[FullDate],
                    BLANK (),
                    MAX ( DimDate[FullDate] )
                )
            )
        )
    )


    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular.

    • Proposed as answer by Daniel Wikar Thursday, October 19, 2017 12:34 PM
    Saturday, May 9, 2015 11:49 AM
  • Thank you, Marco! That is precisely what I was looking for.  Really appreciate your willingness to share your DAX expertise.

    Ben Lezin

    Sunday, May 10, 2015 4:33 PM
  • Hi Marco,

    Thanks for sharing your valuable knowledge and helping people. Further I have a similar requirement and when I tried to use this method somehow its not working for me.

    Below is my data structure

    I am looking forward for a report like below: (yesterday's balance will be next day's opening irrespective of if sale happened on previous day or not- example for ProductID 3 no sales happen post 17th till 21st so it consistently shows 7 as Today & Yesterday balance).

    I tried to work as per the formula above ( I am sure I am wrong) 

    TX:=SUMX(
          Values(Sales[Date]),
          Calculate(
               SUM(Sales[SaleAmt]),
               CALCULATETABLE(
                    LASTNONBLANK(
                             Dates[Dates],
                             calculate(countrows(Sales))
                          ),
                         DATESBETWEEN(
                                 Dates[Dates],
                                 blank(),
                                max(Dates[Dates])
                          )
                      )
                   )
            )

    Seeking your expert advice to understand an unlock this mystery.

    Regards

    Gurpreet Sethi


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++


    • Edited by GURSETHI Tuesday, February 28, 2017 9:43 PM Updates
    Tuesday, February 28, 2017 9:40 PM
  • Probably instead of Values(Sales[Date]) you should iterate by Values(Sales[ProdID])

    I hope it helps.


    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular.

    Wednesday, March 1, 2017 1:50 PM
  • Thanks Marco,

    Well I tried something different

    1) Added missing dates i.e. Saturday & Sunday (18th and 19 Feb)


    2) Created a Measure to calculate SUM of SalesAmt

         SumSaleAmt:=sum(Sales[SaleAmt])

    3) Created a Measure to calculate Todays SalesAmt

          TodayBal:=calculate([SumSaleAmt],calculatetable(lastDate(Sales[Date]),datesbetween(Dates[Dates],blank(),lastdate(Dates[Dates])),ALL(Dates)),ALL(Dates))

    4) Create a Measure to calculate Yesterdas SalesAmt

           YesterdayBal:=calculate([SumSaleAmt],calculatetable(lastDate(Sales[Date]),datesbetween(Dates[Dates],blank(),lastdate(PREVIOUSDAY(Dates[Dates]))),ALL(Dates)),ALL(Dates))

    With this I am able to achive what I am looking for but the only problem is (below screenshots)

    The only problem is for 17th which is first date in my calendar, for that I am getting values (ProductID 1,3) as of 22nd & 23rd. Seems its going in round robin manner.


    Regards

    Gurpreet Sethi


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++



    • Edited by GURSETHI Thursday, March 2, 2017 1:30 AM update
    Wednesday, March 1, 2017 10:44 PM
  •  I am facing the same issue than blezin (the total premium value excludes contracts that don't have values in the context period. Unfortunately the code proposed by Marco does not solve my issue. Here is my code:

    DEFINE MEASURE 'Facteur Marché Etat'[PrimeNetteAvantFacteurMarche_Etat] =

    SUMX

           VALUES('Contrat'[NPA QCBD]),

           CALCULATE

           (

                 SUM('Facteur Marché Etat'[PrimeNetteAvantFacteurMarche])

                ,CALCULATETABLE

                (

                      LASTNONBLANK

                      (

                            VALUES('DATE'[DATE]),

                            CALCULATE(COUNTROWS('Facteur Marché Etat'))

                      ) ,

                      DATESBETWEEN

                      (

                             'Date'[Date],

                              BLANK(),

                              MAX('Date'[Date])

                       )

                )

         )

    )

    //Details

    EVALUATE

    CALCULATETABLE

    (

               SUMMARIZE

               (

               'Facteur Marché' ,

               'Contrat'[NPA QCBD] ,

               "Etat", [PrimeNetteAvantFacteurMarche_Etat] ),

                'Contrat'[NPA QCBD] = "976290/7000" || 'Contrat'[NPA QCBD] = "3435871/7000"

              ,'Date'[Année] = 2019

              --,'Date'[Année Mois] = "2019 08"

              --,'Date'[Date] = Date(2019, 8, 13) )

    I need the last transaction prior to the context period, not just limited to that period.

    Do you understand what is going on?


    • Edited by Stefan Z. _ Friday, August 16, 2019 7:58 AM
    Friday, August 16, 2019 7:46 AM
  • At first sight the measure should work, assuming that Date is marked as a Date table.

    The main issue should be performance, this thread started 5 years ago, today using VAR and TREATAS we can do much better, but if this calculation doesn't work there could be something else to fix first.



    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular.

    Friday, August 16, 2019 3:43 PM
  • Thank you Marco.

    Can you please provide an example?

    Regards

    Stefan

    Saturday, August 17, 2019 1:23 PM