locked
Compute billing in previous period RRS feed

  • Question

  • Hi,

    Imagine a 5 column dataset with the following fields - Organiser, Date of Invoice, Total Invoice Value, Reimbursements and Service tax charged.  Using the PowerPivot, I want to determine the "Growth in Professional fee billed over the previous period" - please note that previous period need not be previous year because a client may be billed after a gap of 1-2 years as well.

    To compute growth, I first need to determine the absolute value of Professional fee billed over the previous period.  This is where I am getting stuck.  Since the billing periods for different clients need not be consecutive, I cannot use the SAMEPERIODLASTYEAR function.

    In trying to solve the problem, I tried to frame a calculated field formula but could not do so.  Therefore, I tried solving it via a calculated column formula in the PowerPivot window.  My idea here was to determine the client wise previous financial year for each row and then use this column in a calculated field formula to get my desired result.  I am getting an error when I write this formula (see Billing data tab of PowerPivot window)

    I have explained my exact requirement in the PowerPivot worksheet of this workbook (http://1drv.ms/1MzD0y5).

    Please help.

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Sunday, June 14, 2015 12:11 AM

Answers

  • LASTNONBLANK would jump the gap in years, but then you need to make sure, that it doesn’t take the LNB in the current Financial year, in case there multiple invoices here. You can use EARLIER on your Financial Year for that.

    So my approach would be this measure:

    PreviousFee:=CALCULATE(LASTNONBLANK(billing_data[Net];1);FILTER(ALL(billing_data);COUNTROWS(FILTER('billing_data';EARLIER([Financial Year])<[Financial Year]&&EARLIER([Organiser])=[Organiser]))))

    But you need to calculate your “ProfessionalFeeCharged” (called “Net” in my example) as a calculated column instead of a measure.

    On how to use EARLIER in a measure: https://javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/


    Imke

    • Marked as answer by Ashish Mathur Tuesday, June 16, 2015 2:43 AM
    Monday, June 15, 2015 8:50 AM
    Answerer
  • Hi,

    Thank you for your help.  This revised calculated field formula gave me the desired result.

    =if(HASONEVALUE(calendar[Financal Year]),CALCULATE(LASTNONBLANK(billing_data[Yearly Professional fee],1),FILTER(ALL(billing_data),COUNTROWS(FILTER(billing_data,EARLIER(billing_data[Financial Year of billing])<billing_data[Financial Year of billing]&&EARLIER(billing_data[Organiser])=billing_data[Organiser])))),BLANK())

    Also, I had to write this calculated column formula first.  I named it as Yearly professional fee

    =SUMX(FILTER(billing_data,EARLIER(billing_data[Financial Year of billing])=billing_data[Financial Year of billing]&&EARLIER(billing_data[Organiser])=billing_data[Organiser]),billing_data[Total invoice Value]-billing_data[Reimbursements]-billing_data[Service tax charged])

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com



    Tuesday, June 16, 2015 2:43 AM
  • Well, LASTNONBLANK on numbers is like MAX: http://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/ - so not for us here.

    Quick & dirty solution:

    2 new columns:

    StartOfFY=LEFT([Financial Year];4)

    LastYear = CALCULATE(LASTNONBLANK(billing_data[StartOfFY];1);

                 FILTER('billing_data';'billing_data'[StartOfFY]<EARLIER(billing_data[StartOfFY])&&[Organiser]=EARLIER([Organiser])))

    Your Measure:

    PrevAmount:=CALCULATE(AVERAGE('billing_data'[Yearly Professional fee]);

          FILTER(ALL('billing_data');billing_data[StartOfFY]=MAX([LastYear]));

          FILTER(ALL(billing_data);COUNTROWS(FILTER(billing_data;EARLIER(billing_data[Organiser])=billing_data[Organiser]))))


    Wednesday, June 17, 2015 6:56 PM
    Answerer

All replies

  • Surely each invoice states the billing periods and its values,
    instead of just the date the invoice was presented.
    Hopefully all billing periods (in FYears, FQuarters, FMonths or days)
    are of the same length so a reasonable comparison can be made.

    Sunday, June 14, 2015 7:56 PM
  • Hi,

    Could you please share the formula with me.  The file can be downloaded from the link shared above.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, June 15, 2015 2:39 AM
  • Excel 2013 Pro Plus
    With new fictitious data.
    Billing period is one Fiscal Year.
    http://www.mediafire.com/view/y4z60o99r1yeg6e/06_13_15.xlsx

    Monday, June 15, 2015 4:50 AM
  • LASTNONBLANK would jump the gap in years, but then you need to make sure, that it doesn’t take the LNB in the current Financial year, in case there multiple invoices here. You can use EARLIER on your Financial Year for that.

    So my approach would be this measure:

    PreviousFee:=CALCULATE(LASTNONBLANK(billing_data[Net];1);FILTER(ALL(billing_data);COUNTROWS(FILTER('billing_data';EARLIER([Financial Year])<[Financial Year]&&EARLIER([Organiser])=[Organiser]))))

    But you need to calculate your “ProfessionalFeeCharged” (called “Net” in my example) as a calculated column instead of a measure.

    On how to use EARLIER in a measure: https://javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/


    Imke

    • Marked as answer by Ashish Mathur Tuesday, June 16, 2015 2:43 AM
    Monday, June 15, 2015 8:50 AM
    Answerer
  • Hi,

    Thank you for your help.  This revised calculated field formula gave me the desired result.

    =if(HASONEVALUE(calendar[Financal Year]),CALCULATE(LASTNONBLANK(billing_data[Yearly Professional fee],1),FILTER(ALL(billing_data),COUNTROWS(FILTER(billing_data,EARLIER(billing_data[Financial Year of billing])<billing_data[Financial Year of billing]&&EARLIER(billing_data[Organiser])=billing_data[Organiser])))),BLANK())

    Also, I had to write this calculated column formula first.  I named it as Yearly professional fee

    =SUMX(FILTER(billing_data,EARLIER(billing_data[Financial Year of billing])=billing_data[Financial Year of billing]&&EARLIER(billing_data[Organiser])=billing_data[Organiser]),billing_data[Total invoice Value]-billing_data[Reimbursements]-billing_data[Service tax charged])

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com



    Tuesday, June 16, 2015 2:43 AM
  • Hi,

    I am facing a problem when years are consecutive.  Please see this workbook (http://1drv.ms/1LhlgYd) for a clear description of the problem.  Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Wednesday, June 17, 2015 12:53 PM
  • Hm, very strange - our formulas deliver the LastBiggest instead of the LastNonBlank (try to type in 1000000 in the first year - it will be shown throughout, then in the second...).

    Need to look this up, but this will need to wait a bit (busy)


    Imke

    Wednesday, June 17, 2015 3:37 PM
    Answerer
  • Well, LASTNONBLANK on numbers is like MAX: http://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/ - so not for us here.

    Quick & dirty solution:

    2 new columns:

    StartOfFY=LEFT([Financial Year];4)

    LastYear = CALCULATE(LASTNONBLANK(billing_data[StartOfFY];1);

                 FILTER('billing_data';'billing_data'[StartOfFY]<EARLIER(billing_data[StartOfFY])&&[Organiser]=EARLIER([Organiser])))

    Your Measure:

    PrevAmount:=CALCULATE(AVERAGE('billing_data'[Yearly Professional fee]);

          FILTER(ALL('billing_data');billing_data[StartOfFY]=MAX([LastYear]));

          FILTER(ALL(billing_data);COUNTROWS(FILTER(billing_data;EARLIER(billing_data[Organiser])=billing_data[Organiser]))))


    Wednesday, June 17, 2015 6:56 PM
    Answerer
  • Hi,

    This works very well.  Thank you for your help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, June 20, 2015 6:11 AM