locked
Daily Inventory Balance Using DAX - Fact table only has balance if balance has changed RRS feed

  • Question

  • I have a product inventory fact table that has an entry for all month-end dates and for all dates that the inventory balance has changed.  The problem that I have is that I need to generate a report that shows daily balances for all days.

    I believe the following DAX query should return the results that I want, but it leaves blank entries for days that have no daily balance.  The logic behind this query is that I know I have an entry for the previous month-end date.   If the balance has change then I'll have a more recent entry.  I expect a result for every day whether the balance changed or not.

    Inventory Balance (LastDate):=
    CALCULATE(
    	SUM( 'Product Inventory'[Units Balance] ),
    	ALL('Date'),
    	LASTDATE( DATESBETWEEN('Date'[Date], LASTDATE(PREVIOUSMONTH('Date'[Date])), LASTDATE('Date'[Date])))
    )

    My result with missing days is below.  Notice I don't have entries for 7/3 and 7/4 and I have blanks for other days.  I want to see entries for all days.

    I have reproduced my scenario (FactProductInventory) using the AdventureWorksDW2012 database and this query to populate my cube's inventory table. 

    select a.ProductKey, a.MovementDate as 'Balance Date', a.UnitsBalance  as 'Units Balance'
    from 
    (
      select *, lag(UnitsBalance, 1) over (Partition by ProductKey order by DateKey) as 'PreviousBalance'
      from FactProductInventory
      where ProductKey in (310, 346, 351)
    ) a
    where 1=1
    and (a.UnitsBalance <> a.PreviousBalance or EOMONTH(a.MovementDate) = a.MovementDate)
    and a.MovementDate between '20050601' and '20050831'

    Below is a screenshot of the data that the above query has returned in my cube and a table diagram of my sample cube.

    Any help would be greatly appreciated.

    Thanks,

    Bill Hoenig

    Tuesday, December 17, 2013 6:47 PM

Answers

  • Ok, my fault, I wrote the code without testing :)

    Here goes the updated code. Replace the server column with the product column and diskspaceQty with your measure. Tested it and it works

    =sumx(ADDCOLUMNS(ADDCOLUMNS(crossjoin(values('Server'[Server]), values('Date'[Date])), "LNEDate", calculate(max(Fact[Date]), filter(ALL('Date'), 'Date'[Date]<=MAX('Date'[Date])))), "DiskSpaceQty", CALCULATE(sum(Fact[Disk Space]), FILTER(ALL('Date'), 'Date'[Date] = [LNEDate]))), [DiskSpaceQty])


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by bhoenig Wednesday, December 18, 2013 1:08 AM
    Tuesday, December 17, 2013 10:24 PM
    Answerer

All replies

  • Try using the LASTDATE for the Fact[Date]

    nventory Balance (LastDate):=
    CALCULATE(
    	SUM( 'Product Inventory'[Units Balance] ),
    	ALL('Date'),
    	LASTDATE( DATESBETWEEN('Date'[Date], LASTDATE(PREVIOUSMONTH('Date'[Date])), LASTDATE('Product Inventory'[Balance Date])))
    )

    The reason is that if you are at the date 7/3/2005, the LASTDATE in the previous formula would return 7/3/2005, and you dont have units balance for that date. Hence it shows as NULL.

    In the new formula, even if you select 7/3/2005, LASTDATE returns 7/2/2005 as that is the last date which has value for Balance Date in the fact. And now it will display the value for 7/2/2005.

    I didn't try this, but it should work. If it doesn't, let me know and I can take a look at it.

    Good read on this topic by the way - 

    http://road-blogs.blogspot.com/2012/06/lastnonempty-in-tabular-mode-part-1.html

    http://javierguillen.wordpress.com/2012/06/09/lastnonempty-in-tabular-mode-part-2-last-ever-non-empty-calculations-in-dax/


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Tuesday, December 17, 2013 7:34 PM
    Answerer
  • Thanks Jason for the quick response.  I tried your formula and it does get me daily data, but it's always for the last date in the database.  Also, it returns every date that I have in my sample database.  My Date table starts at 6/1/2005 and my Product Inventory starts at 6/30/2005.  The numbers that you see are the ending balances.

    I'm going to do some more research and read your recommended links. 

    Thanks again,

    Bill

    Tuesday, December 17, 2013 8:27 PM
  • Ok, my fault, I wrote the code without testing :)

    Here goes the updated code. Replace the server column with the product column and diskspaceQty with your measure. Tested it and it works

    =sumx(ADDCOLUMNS(ADDCOLUMNS(crossjoin(values('Server'[Server]), values('Date'[Date])), "LNEDate", calculate(max(Fact[Date]), filter(ALL('Date'), 'Date'[Date]<=MAX('Date'[Date])))), "DiskSpaceQty", CALCULATE(sum(Fact[Disk Space]), FILTER(ALL('Date'), 'Date'[Date] = [LNEDate]))), [DiskSpaceQty])


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by bhoenig Wednesday, December 18, 2013 1:08 AM
    Tuesday, December 17, 2013 10:24 PM
    Answerer
  • Thanks Jason,

    The query you provide worked great in my sample database.  It was exactly what I wanted.  Once I put this in my production database with about 900,000 products and about 77,000,000 fact entries, it never finishes.  At this point I really have to question the design of my cube.  I have to come up with a better way to get daily balances. 

    I do appreciate your response and learned a lot through this process. 

    Bill 

    Wednesday, December 18, 2013 1:08 AM
  • Hi Julian,

    I have tried using the LASTNONBLANK, but it doesn't give me a value for days that have no entries.  The LASTNONBLANK only will give a value if there is a value in the context of the reports filters.  I'm filtering down to the day level.  This works great at the Month or Year level because my data has at least one entry within a given month or year.  The root cause of my problem is I don't have a entry within every day context.

    I'm curious to know how other people with large data warehouses are handling this scenario.  I'm leaning towards capturing daily balances, but only keeping the daily balances for a few months, then purge the non-month-end entries.  At first thougth, I could probably do this with Partitions and some sql logic.

    Thanks,

    Bill


    • Edited by bhoenig Wednesday, December 18, 2013 1:37 AM grammer
    Wednesday, December 18, 2013 1:34 AM
  • I did solve my DAX performance issue.  I think it’s worth noting, since the marked solution will only work with a small datasets.

    What my data was missing was a “BalanceChange” column.  Since my source data didn’t have this data, I used SQL to calculate it.  I used the following:

    select *
    , 'BalanceChange' = 
      case
        when LAG(fpi.[Units Balance]) over (partition by fpi.ProductKey order by fpi.[Balance Date]) is null  
            and [Balance Date] <> EOMONTH([Balance Date])
          then fpi.[Units Balance]
        else fpi.[Units Balance] - LAG(fpi.[Units Balance]) over (partition by fpi.ProductKey order by fpi.[Balance Date])
      end
    from factProductInventory fpi
    

    Since my data always has a month-end entry, I can rely on an Opening Balance.  Every day in a given month will have the same opening balance (the balance at the end of prior month).  With the formula below, I get an entry for every day of the month. 

    Opening Balance:=OPENINGBALANCEMONTH(SUM('Product Inventory'[Units Balance]), 'Date'[Date])

    I then get the month-to-date (MTD) change so that we can add it to the Opening Balance.

    Balance MTD Change:=TOTALMTD(SUM('Product Inventory'[BalanceChange]), 'Date'[Date])


    Finally I add the two together (I had to throw in a little extra logic to avoid future dates and to avoid incorrect column totals).

    Inventory Balance:=IF(
    	LASTDATE('Date'[Date]) > TODAY()
    	, BLANK()
    	, [Opening Balance] + IF(COUNTROWS(VALUES('Date'[MonthNumberOfYear])) = 1, [Balance MTD Change], BLANK())
    )

    All this said, I'll warn anyone that reads this that if your fact table has two columns that can change based on a given day, then you need to go to daily balances.  I say this because in my real world scenario, I'm dealing with Account Balances and those accounts have AccountTypes that can change too.  To restate this, my fact table is tracking two changing columns (Account Balances and Account Types).  Because of this second column changing on me, I had accounts showing up twice if the Account Type changed during the month and the user was slicing by Account Type.  My final solution was to use sql logic to generate a new fact table that contained daily balances.

    I hope this will be helpful for someone in the future.

    Regards,

    Bill


    Wednesday, January 1, 2014 11:35 PM