locked
Dynamic closingbalance measure time intelligence RRS feed

  • Question

  • Hello, In my Debts table I keep snapshots of Customers debt balances. I keep two snapshots per week in order to see whats happenning to debts. Each snapshot is inserted to the fact table with run_date field. run_date has relationship to dimDate table for time intelligence purposes I would like to analyze the trends by year, month and date by drillingdown run_date hierarchy So I would like to build a measure that it would show for each snapshot date its amount, for each month - the amount for THE LAST SNAPSHOT DATE THAT MONTH (not necessarily 30/31 of month, because snapshots dont run every day) I tried LASTDATE(dimDate[DateKey)) as in this post: http://blogs.adatis.co.uk/blogs/jeremykashel/archive/2010/02/11/dax-closing-balances.aspx but it works only when theres a snapshot on the last date of a month (30/31) If the last snapshot of March was on 29 then Lastdate will return blank because in DimDate table there are all the dates by design, so it filters by March 31.., I need smthing like LastNonEmpty... Thanks in advance Michael Shparber

    Michael

    Wednesday, March 13, 2013 8:42 PM

Answers

  • I think I got it!!!

    CALCULATE +( LASTNONBLANK + ( CALCULATE +(  ALLEXCEPT)) !!!

    EOPDebt:=CALCULATE(SUM(Debt_Fact[DebtAmt]),LASTNONBLANK(Calendar_Table[DateKey],CALCULATE(SUM(Debt_Fact[DebtAmt]),ALLEXCEPT(Debt_Fact,Calendar_Table[DateKey]))))

    Am I missing smthng?

    Thanks!

    Michael Shparber


    Michael


    • Edited by M. Shparber Sunday, March 17, 2013 2:54 AM more accurate
    • Marked as answer by M. Shparber Wednesday, April 10, 2013 4:38 AM
    Sunday, March 17, 2013 2:53 AM

All replies

  • Michael,

    I approached this problem on the basis that you would be using an external date table that is related to your 'Debt' table by date which also has a column called month number. Under these circumstances there's an extremely simple solution to this problem using a function called TOPN which establishes in that context the highest value. By using the Month number from the date table as the Row Label in your Pivot and referencing the date from the 'Debt' table it returns what you are looking for.

    I created 2 measures, one to sum the value of the debts and one to filter the debt table to the 'highest' date in the month to just return one value:

    [DebtMes] = SUM(Debt[Debt])
    [Last Snap Shot] = CALCULATE([DebtMes],TOPN(1,Debt,Debt[Date]))

    I built a quick demo here: https://skydrive.live.com/redir?resid=ABABDB39DCA6062!210&authkey=!ALkn_p94gc3Jsto 

    Regards

    Jacob

    Friday, March 15, 2013 3:52 AM
  • Thanks Jacob,

    That's a creative solution and it works!

    Meanwhile I've come up with LASTNONBLANK:

    DebtBalance:=CALCULATE(sum([Debt]),LASTNONBLANK(Calendar_Table[DateKey],CALCULATE(COUNTROWS(Fact_Debts),RELATEDTABLE(Fact_Debts))))

    Both give the same result BUT... I've ran into another problem:

    In my Debt table there are customers. If a customer pays off his debt - there's no row for him anymore in the following snapshots

    Because TOPN and LASTNONBLANK are aware of filter context, when I put Customers and Months in Pivot, it shows the LAST Debt of a customer as a closing balance and not the debt ON THE LAST SNAPSHOT for ALL CUSTOMERS that month.

    For example, Customer A owes $100 on 05-March, 10-March and 28-March (and lets say those all the snapshots I have for March.

    Customer B, owed $200 on 05-March but has paid the debt so he doesn't show up on 10 and 28 - March snapshots

    If I use the above formulas, I will have a total closing debt for March = $100 (which is OK), $100 for Customer A (also OK) and $200 for Customer B (which is NOT OK, since I expect 0 or blank - he doesn't have debt on 28-March)

    Please help

    Thank you

    Michael Shparber


    Michael

    Friday, March 15, 2013 10:38 PM
  • Michael,

    That's a good point! I had another look and came up with an alternative technique:

    [MaxDate] = CALCULATE(MAX(Debt[Date]),ALL(Debt[Customer]),ALLEXCEPT('Date','Date'[Month]))
    
    [Last Snap Shot] = CALCULATE([DebtMes],FILTER('Date', 'Date'[Date] = [MaxDate]))
    
    

    Basically I created a measure to establish the max date within that month and then used that date to filter the date table to only return stuff from that date. Seems to do the trick.

    I uploaded an updated example model on Skydrive with a amended dataset and the new measures: Example Model

    HTH

    Jacob

    • Proposed as answer by Jacob Barnett Wednesday, April 10, 2013 4:27 AM
    • Unproposed as answer by M. Shparber Wednesday, April 10, 2013 4:38 AM
    Saturday, March 16, 2013 5:01 AM
  • Thanks Jacob,

    That does work, but I am still missing some features:

    1. It has to show the closing balance for each period (I probably wasn't specific enough earlier), not only month: year, date...

    If I look at Year level, I'd like to see tha last snapshot for that year. If I drill to date level - the debts for EACH snapshot. The FILTER function you've built leaves just the LAST snapshot dates for every month

    2. ALL(Debt[Customer]) in not enough - there are many attributes in the Fact table - product, debt_type, due date, billing_date, etc.

    I've tried playing with ALLEXCEPT, but no success.

    Also, it seems like CALCULATE (....., FILTER...) takes a lot of time. I have only 4 million records (lots of debts :) and a very powerful server (128 GB RAM) - So it should be a piece of cake, but still - takes a couple of minutes

    I'm confident - there's an elegant solution out there...

    Any ideas?

    Thanks

    Michael Shparber


    Michael

    Sunday, March 17, 2013 2:12 AM
  • I think I got it!!!

    CALCULATE +( LASTNONBLANK + ( CALCULATE +(  ALLEXCEPT)) !!!

    EOPDebt:=CALCULATE(SUM(Debt_Fact[DebtAmt]),LASTNONBLANK(Calendar_Table[DateKey],CALCULATE(SUM(Debt_Fact[DebtAmt]),ALLEXCEPT(Debt_Fact,Calendar_Table[DateKey]))))

    Am I missing smthng?

    Thanks!

    Michael Shparber


    Michael


    • Edited by M. Shparber Sunday, March 17, 2013 2:54 AM more accurate
    • Marked as answer by M. Shparber Wednesday, April 10, 2013 4:38 AM
    Sunday, March 17, 2013 2:53 AM
  • Michael,

    The ALL(Debt[Customer]) is purely designed to find the latest date that exists within that context regardless of customer, I don't see that the other columns would be relevant (although could be included if desired). As for being able to look at more than just months all you would need to do is adjust the ALLEXCEPT to just ALL on the date column (as you've effectively done in your formula). 

    On 4M rows I'd expect this to resolve comfortably within 10 seconds.

    PowerPivot is optimized to work best with 'tall and skinny' fact tables, if your fact table was, say, 15 columns then that will impact performance.

    Jacob

    Sunday, March 17, 2013 4:00 AM