locked
measure based on dynamic effective date (sum as at) help RRS feed

  • Question

  • cross post from the SSAS forum because i figure the dax gurus probably spend more time here : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/45208f92-6c71-4b9e-9fd0-05b9beaba0b7/tabular-fast-patterns-for-dynamic-banding-of-measures-by-effective-date?forum=sqlanalysisservices

    I'm trying to set up an aged debtor band report.

    Step 1 is figuring out how much was owed and paid as at a particular date.

    But the dax i'm writing is EXTREMELY slow. minutes+ for a single invoice.

    The 2012 blog i used to write it suggested this:

    Payment At Date :=
    CALCULATE (
        SUM ( [Payment Amount] ),
        USERELATIONSHIP ( 'Reporting Date'[Reporting Date], Receipts[Receipt Business Date] ),
        FILTER (
            ALL ( 'Reporting Date'[Reporting Date] ),
            COUNTROWS (
                FILTER (
                    'Reporting Date',
                    EARLIER ( 'Reporting Date'[Reporting Date] )
                        <= MAX ( 'Reporting Date'[Reporting Date] )
                )
            )
                > 0
        )
    )

    I've tried simplifying it to

    Payment At Date :=
    CALCULATE (
        SUM ( [Payment Amount] ),
        USERELATIONSHIP ( 'Reporting Date'[Reporting Date], Receipts[Receipt Business Date] ),
        FILTER (
            ALL ( 'Reporting Date'),
            'Reporting Date'[Reporting Date]
                <= MAX ( 'Reporting Date'[Reporting Date] )
        )
    )

    but both are very slow. And what's the difference? is using countrows meant to have some kind of performance improvement?

    As in waiting for minutes when a new attribute is added/removed to a pivot table that is filtered to a single invoice (~500k all together, 320meg model). I've seen some blogs that using VALUES(Receipts[Receipt Business Date]) to "prefilter" might speed it up but haven't been able to implement it in a way that doesn't spit out the wrong value. It only works if i select the exact date of the receipt business date

    The way i use it is i add the Reporting Date dim as a slicer then choose a date (either a year, month or day). Even when nothing's selected it still takes just as long. I tried adding if(ISCROSSFILTERED('Reporting Date'[Reporting Date]),complex dax,SUM(Payment Amount)) but it didn't make any difference.

    Any tips or do I go back to client and say that they can only report aged debtors at the current snapshot, no historical reporting from the semantic model, because with the current performance it's not feasible

    Thanks


    Jakub @ Adelaide, Australia Blog

    Wednesday, February 25, 2015 3:37 AM

Answers

  • Heh, I think I know why That skydive acc was linked to my org o365 account that's probably been disabled I've left that consulting company to join a startup I guess that makes this problem no longer relevant to my interests. I don't think I have my mockup workbook saved to my personal laptop either But thanks for trying to have a look

    Jakub @ Adelaide, Australia Blog

    • Proposed as answer by Michael Amadi Tuesday, April 28, 2015 12:12 PM
    • Marked as answer by Michael Amadi Friday, May 8, 2015 10:45 AM
    Tuesday, April 28, 2015 11:17 AM

All replies

  • you might try this calculation

    Payment At Date :=
    CALCULATE (
        SUM ( [Payment Amount] ),
        USERELATIONSHIP ( 'Reporting Date'[Reporting Date], Receipts[Receipt Business Date] ),
        DATESBETWEEN ( 'Reporting Date', BLANK(), MAX ( 'Reporting Date'[Reporting Date] ) )
    )

    however, the behavior that a query takes several minutes seems a bit strange to me for a calculation like this and - without knowing your report or model in detail - i would assume that the problem is something else

    can you strip down your pivottable to only contain that one particular measure and remove all unnecessary things and rerun the query?

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Wednesday, February 25, 2015 7:20 AM
    Answerer
  • Hi Gerhard,

    Thanks for the reply, i'll give that code a shot.

    Yes, if i strip everything except the measure then it does return in about 2s when i select a single date. Clearing the slicer/filter takes about 40sec to refresh. But that kind of defeats the purpose of knowing who owes what and how slow they are at paying. the measure will be looked at at the individual invoice level most of the time.

    When I filter the pivot table to a single invoice out of the 500k and drag across the invoiceid attribute it takes 2-4sec to refresh if i select a single date, and the same long amount of time if i clear the slicer

    I'm guessing this is because it's using my entire date dim to for the relationship.. Is there a way to limit this further by only focusing on the Receipts'[Receipt Business Date] that appear in my filtered receipts table? eg by forcing values(Receipts'[Receipt Business Date]) in somewhere.

    again i tried adding if(iscrossfiltered('reporting date'[reporting date]),the as at date dax,sum(payment amount)) but it made no difference whether i had it in there or not - i would've thought that this would skip the slow "as at dax" and simply return the simple sum(payment amount) result

    The other problem is that this is only half the battle, this only tells me how much has been paid, not how much was owed, or it if nets to 0 (therefore should be excluded).

    Once I add those calcs it balloons to many minutes.


    Jakub @ Adelaide, Australia Blog

    Thursday, February 26, 2015 12:26 AM
  • you may remove the relationship between your Receipts-table and your date-table and use this calc:

    Payment At Date :=
    CALCULATE (
        SUM ( [Payment Amount] ),
        DATESBETWEEN ('Receipts'[Receipt Business Date], BLANK(), MAX ( 'Reporting Date'[Reporting Date] ) )
    )

    maybe u can share an anonymized sample of your data?
    oterwise this is a bit hard to debug

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, March 2, 2015 3:41 PM
    Answerer
  • thanks gerhard,

    on holidays at the moment, but i'll see what i can knock together when I get back in a weeks' time

    Thanks


    Jakub @ Adelaide, Australia Blog

    Monday, March 9, 2015 4:41 AM
  • Hi Jakubk,

    Have you been able to check whether Gerhard's solution solves your problem?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Friday, March 20, 2015 9:37 AM
  • Hi,

    yes, a bit faster than my original code and the subsequent suggestions for that one scenario. But once i plugged everything in and used the banded 'aged debtor' measure it wasn't feasible..

    It's not longer a problem for the current project im working on - the requirement no longer applies due to data quality issues with the 'payment date', but i'm still curious as to whether this is possible to do using tabular in a manner that is performant.

    I'll try to throw together an anonymised ppvt workbook this afternoon


    Jakub @ Adelaide, Australia Blog

    Monday, March 23, 2015 12:08 AM
  • OK, thanks for the update. An anonymised workbook would be great because we'll be able to look at the query plan which may lead to a more optimal solution.

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Monday, March 23, 2015 6:42 AM
  • ok, finally have a workbook uploaded here http://1drv.ms/1CTIOTd

    the filtered pivots (based on the reporting date) should only show a single invoice out of the 400k or so in there.

    dragging across a single attribute takes forever (i haven't had the patience to wait it out). And that's just on the base measures.

    Takes even longer once i do the aged debt report (the second tab). I know there's some logic bugs in there - eg it's using the invoice date instead of the due date, but i haven't begun to debug that because the performance makes the report unfeasible.

    Thanks


    Jakub @ Adelaide, Australia Blog

    Tuesday, March 24, 2015 6:33 AM
  • Hi jakubk,

    I can't seem to download the file (which is strange). Any chance you could upload it to OneDrive zipped and provide a link to that?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, April 28, 2015 9:17 AM
  • Heh, I think I know why That skydive acc was linked to my org o365 account that's probably been disabled I've left that consulting company to join a startup I guess that makes this problem no longer relevant to my interests. I don't think I have my mockup workbook saved to my personal laptop either But thanks for trying to have a look

    Jakub @ Adelaide, Australia Blog

    • Proposed as answer by Michael Amadi Tuesday, April 28, 2015 12:12 PM
    • Marked as answer by Michael Amadi Friday, May 8, 2015 10:45 AM
    Tuesday, April 28, 2015 11:17 AM
  • Ok, thanks for the update.

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, April 28, 2015 12:12 PM