locked
Customer Debt Analytics - DAX practice - bonus exercise :) RRS feed

  • Question

  • OK, here's a bonus exercise :)

    I've got a Debt_Fact table (from the previous post : http://social.technet.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/a9904962-ed5d-4c7c-9ee4-88b506732e5c)

    with snapshots of customer debts. There're multiple rows per customer per snapshot - no PK

    Snapshot date column - Debt_Fact[RUN-DATE] is related to the Date Table - Calendar_Table for time-intelligence purposes

    I want to build a report that contains the following measures - for any chosen month in filter (month only, not date, not year):

    1. Debts_BeginningOfPeriod

    2. !!! Debts_EndOfPeriod_SameCustomers - will calculate the end-of-period debts only for the customers that had debts in the beginning of period (the same customers that have the first measure non-empty)

    3. Debts_EndOfPeriod_NewCustomers - will show new debts - customers that didn't have debts earlier, but do at the end-of-period

    4. And, of course, Total_Debts_EndOfPeriod

    I've built this report in SSRS, but I want to practice DAX and PIVOT - it seems to has been built for these kind of tricks...

    Ideas?

    Thank you

    Michael Shparber


    Michael


    • Edited by M. Shparber Sunday, March 17, 2013 3:25 AM more data
    Sunday, March 17, 2013 3:17 AM

Answers

  • Sorry, I had jumped over to your other thread and forgot to double check your requirements on this one.

    To do points 2 & 3 I would simply build on the 2 base calculations filtering the customers down to those who meet your business rules.

    Debts_EndOfPeriod_SameCustomers:=CALCULATE([Closing Debt], FILTER(customers, not(ISBLANK([Opening Debt])) && not(ISBLANK([Closing Debt]))))

    Debts_EndOfPeriod_NewCustomers:=CALCULATE([Closing Debt], FILTER(customers, ISBLANK([Opening Debt]) && not(ISBLANK([Closing Debt]))))


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by M. Shparber Thursday, December 19, 2013 10:41 PM
    Wednesday, December 18, 2013 11:05 PM

All replies

  • Michael,

    Is this still an issue?

    Thank you!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, November 24, 2013 2:20 AM
  • We did solve it somehow but I am far from sure that it was the efficient way

    Michael

    Monday, November 25, 2013 6:18 AM
  • We did solve it somehow but I am far from sure that it was the efficient way

    Michael

    So did you want to see if another solution exists? Or have you moved on?

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, November 29, 2013 11:23 PM
  • We solved it in SSRS and I would be glad to see the DAX solution Thanks!

    Michael

    Saturday, November 30, 2013 8:57 AM
  • Does anyone have a DAX solution for Michael?

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, December 16, 2013 7:01 PM
  • So based on the following sample data that I set up. 

    I created the following 2 measures: 

    Opening Debt:=CALCULATE(sum(Debts[Debt]),Filter('Dates','Dates'[CalendarDate] = minx(values(Dates[CalendarDate]),Dates[CalendarDate])))

    Closing Debt:=CALCULATE(sum(Debts[Debt]),Filter('Dates','Dates'[CalendarDate] = minx(values(Dates[CalendarDate]),Dates[CalendarDate])))

    which return the following results in a pivot table when queried at the month, day and year levels. I'm pretty sure that does everything you wanted. 


    http://darren.gosbell.com - please mark correct answers

    Wednesday, December 18, 2013 3:24 AM
  • Thanks Darren, The real challenge are my points 2 and 3... I need to calculate separate debts for new and already existing customers based on a dynamic beginning-of-period and end-of-period comparison I do it in ETL now, but it's not dynamic and I am confident that it's possible in DAX Thanks a lot Michael Shparber

    Michael

    Wednesday, December 18, 2013 8:44 PM
  • Sorry, I had jumped over to your other thread and forgot to double check your requirements on this one.

    To do points 2 & 3 I would simply build on the 2 base calculations filtering the customers down to those who meet your business rules.

    Debts_EndOfPeriod_SameCustomers:=CALCULATE([Closing Debt], FILTER(customers, not(ISBLANK([Opening Debt])) && not(ISBLANK([Closing Debt]))))

    Debts_EndOfPeriod_NewCustomers:=CALCULATE([Closing Debt], FILTER(customers, ISBLANK([Opening Debt]) && not(ISBLANK([Closing Debt]))))


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by M. Shparber Thursday, December 19, 2013 10:41 PM
    Wednesday, December 18, 2013 11:05 PM
  • Very nice Darren,

    Thanks for the elegant solution!

    Michael Shparber


    Michael

    Thursday, December 19, 2013 10:42 PM
  • Sorry, I had jumped over to your other thread and forgot to double check your requirements on this one.

    To do points 2 & 3 I would simply build on the 2 base calculations filtering the customers down to those who meet your business rules.

    Debts_EndOfPeriod_SameCustomers:=CALCULATE([Closing Debt], FILTER(customers, not(ISBLANK([Opening Debt])) && not(ISBLANK([Closing Debt]))))

    Debts_EndOfPeriod_NewCustomers:=CALCULATE([Closing Debt], FILTER(customers, ISBLANK([Opening Debt]) && not(ISBLANK([Closing Debt]))))


    http://darren.gosbell.com - please mark correct answers


    This solution could make a good Wiki article: http://social.technet.microsoft.com/Forums/sqlserver/en-US/eeae7a3f-c841-4b6d-a791-4fb69e81a348/be-our-december-technet-guru-give-the-gift-of-code?forum=sqlkjpowerpivotforexcel

    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, December 19, 2013 11:15 PM