none
Determine the last date of visit before FY selected in the slicer

    Question

  • Hi,

    I have data for date wise clients visits.  My objective is to determine the last date of visit to the client before the FY selected in the slicer.

    The objective, data layout and question is available in this file

    http://sdrv.ms/19RnW0A

    Please help with the appropriate PowerPivot calculated Filed formula.

    Thank you.


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

    Friday, January 24, 2014 2:06 AM

Answers

  • most time-intelligence functions only work correctly if you have marked a table as datetable and set the date-column accordingly 

    therefor it is also a best practice that if you have a date-table that you also mark it as "Date Table"


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

    Monday, February 03, 2014 8:55 AM
    Answerer
  • Hi Ashish,

    You can try the following solution...

    I have changed the DAX formula for the Data[Financial Year] and Data[Financial Year Key] columns because they did not return the expected values based on what you explained to be the financial year.

    The Financial Year column has been re-defined as follows:

    =IF(
      (YEAR(Calendar[Date]) *100)+ MONTH(Calendar[Date]) > (YEAR(Calendar[Date]) *100) + 3 
        && (YEAR(Calendar[Date]) *100) + MONTH(Calendar[Date]) <= ((YEAR(Calendar[Date]) + 1) *100) + 3,
      YEAR(Calendar[Date]) + 1,
      YEAR(Calendar[Date])
    )


    The Financial Year Key has been re-defined as follows:

    =Calendar[FinancialYear] -1 & "-" & Calendar[FinancialYear]

    I have then created a calculated measure called [Last Visit Last Year]:

    Last Visit Last Year:=
    IF(
      FIRSTDATE(ALL('Calendar'[Date])) < FIRSTDATE('Calendar'[Date]), CALCULATE(
        LASTDATE('Calendar'[Date]),
        LASTNONBLANK(
          DATESBETWEEN(
            'Calendar'[Date],
            DATEADD(FIRSTDATE('Calendar'[Date]), -1, YEAR),
            DATEADD(LASTDATE('Calendar'[Date]), -1, YEAR)
          ),
          CALCULATE(COUNTROWS('Data'))
        )
      ), 
      BLANK()
    )

    The outcome can be seen below:

    I'll provide an explanation in another post soon. Hope this helps.

    Michael


    Friday, January 24, 2014 1:32 PM
    Answerer
  • Hi, basically this calculation would solve your initial question, though, it does not match the requested results:

    LastVisit:=CALCULATE(MAX(Data[Date]), DATESBETWEEN(Calendar[Date], BLANK(), MIN(Calendar[Date])-1))

    its important to add that this calculation only works if you mark your Calendar-table as Date-table which was not the case in the sample workbook!

    assuming you select 2012-2013, then according to your Calendar-table you select dates from 31/12/2012 till 30/12/2013

    so the "last date of visit to the client before the FY selected in the slicer." would be the last visit before 31/12/2012 which would be 12/12/2012 for Client A and not 25/02/2012 as expected result


    hth,
    gerhard


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


    Monday, January 27, 2014 9:33 PM
    Answerer
  • Hi,

    First of all thank you for replying and I apologize for the delay in posting back.  Your solution works well and infact get me the exact result.  The reason you are not getting the result is my mistake in the calculated column formula in the calendar table.  The correct formula should be:

    =(MONTH(Calendar[Date])<=3,YEAR(Calendar[Date]),YEAR(Calendar[Date])+1)

    My previous incorrect formula was:

    =(MONTH(Calendar[Date])<=3,YEAR(Calendar[Date]),YEAR(Calendar[Date]+1))

    The moment I carried that correction, I got the expected result.

    Thank you once again.


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

    Saturday, February 01, 2014 6:11 AM

All replies

  • Hi Ashish,

    You can try the following solution...

    I have changed the DAX formula for the Data[Financial Year] and Data[Financial Year Key] columns because they did not return the expected values based on what you explained to be the financial year.

    The Financial Year column has been re-defined as follows:

    =IF(
      (YEAR(Calendar[Date]) *100)+ MONTH(Calendar[Date]) > (YEAR(Calendar[Date]) *100) + 3 
        && (YEAR(Calendar[Date]) *100) + MONTH(Calendar[Date]) <= ((YEAR(Calendar[Date]) + 1) *100) + 3,
      YEAR(Calendar[Date]) + 1,
      YEAR(Calendar[Date])
    )


    The Financial Year Key has been re-defined as follows:

    =Calendar[FinancialYear] -1 & "-" & Calendar[FinancialYear]

    I have then created a calculated measure called [Last Visit Last Year]:

    Last Visit Last Year:=
    IF(
      FIRSTDATE(ALL('Calendar'[Date])) < FIRSTDATE('Calendar'[Date]), CALCULATE(
        LASTDATE('Calendar'[Date]),
        LASTNONBLANK(
          DATESBETWEEN(
            'Calendar'[Date],
            DATEADD(FIRSTDATE('Calendar'[Date]), -1, YEAR),
            DATEADD(LASTDATE('Calendar'[Date]), -1, YEAR)
          ),
          CALCULATE(COUNTROWS('Data'))
        )
      ), 
      BLANK()
    )

    The outcome can be seen below:

    I'll provide an explanation in another post soon. Hope this helps.

    Michael


    Friday, January 24, 2014 1:32 PM
    Answerer
  • Below is the explanation of the solution:

    The DAX formula in the [Financial Year] column ensures that each row in the 'Calendar' table is placed into the correct financial year-end by checking the combination of the year and the month numbers. If the combined year and month number is greater than the month of March in the previous year and less than or equal to the month of march in the current year, then it will return the financial year-end of the current year e.g. '1/2/13' would be given the financial year end of '2013'. Otherwise, the previous year’s value is returned for the year-end.

    As before, the [Financial Year Key] column will return the financial period derived from the financial year-end e.g. the financial year end '2013' means financial year period '2012/2013'. Since this column is being used in the slicer, it was important to ensure that when we filter the 'Calendar' table by a single value in this column, it will only return the dates that correspond to the financial year period.

    The [Last Visit Last Year] measure firstly checks whether the financial year period selected would result in a date range that is earlier than the first date in the 'Calendar' table after being shifted back by 1 year. If this is the case, then no value will be returned i.e. BLANK() is returned. In the case that shifting the implicitly select date range back by 1 year will not result in a date range outside of the 'Calendar' table's values, it will get all dates between the first and last date associated with the current period selection (e.g. 2012/2013) and then find the last date that has an entry in the 'Data' table. Since you are placing Clients on rows, this calculation will be evaluated within this context and return the last date from the previous financial year for each client where there is data in the 'Data' table.

    Hope this helps.

    Michael


    Monday, January 27, 2014 11:03 AM
    Answerer
  • Hi, basically this calculation would solve your initial question, though, it does not match the requested results:

    LastVisit:=CALCULATE(MAX(Data[Date]), DATESBETWEEN(Calendar[Date], BLANK(), MIN(Calendar[Date])-1))

    its important to add that this calculation only works if you mark your Calendar-table as Date-table which was not the case in the sample workbook!

    assuming you select 2012-2013, then according to your Calendar-table you select dates from 31/12/2012 till 30/12/2013

    so the "last date of visit to the client before the FY selected in the slicer." would be the last visit before 31/12/2012 which would be 12/12/2012 for Client A and not 25/02/2012 as expected result


    hth,
    gerhard


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


    Monday, January 27, 2014 9:33 PM
    Answerer
  • Hi, basically this calculation would solve your initial question, though, it does not match the requested results:

    LastVisit:=CALCULATE(MAX(Data[Date]), DATESBETWEEN(Calendar[Date], BLANK(), MIN(Calendar[Date])-1))

    its important to add that this calculation only works if you mark your Calendar-table as Date-table which was not the case in the sample workbook!

    assuming you select 2012-2013, then according to your Calendar-table you select dates from 31/12/2012 till 30/12/2013

    so the "last date of visit to the client before the FY selected in the slicer." would be the last visit before 31/12/2012 which would be 12/12/2012 for Client A and not 25/02/2012 as expected result


    hth,
    gerhard


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


    Gerhard's approach is a nice clean solution.

    The only thing I would add is that we could use LASTDATE and FIRSTDATE instead of the MIN/MAX functions:

    LastVisit:=
    CALCULATE(
      LASTDATE(Data[Date]), 
      DATESBETWEEN(
        Calendar[Date], 
        BLANK(), 
        FIRSTDATE(Calendar[Date]) -1)
    )

    In order to achieve the overall outcome described in the uploaded Excel workbook, changes to the how the [Financial Year] and [Financial Year Key] are calculated will also need to be made (so that the slicer filters the correct data ranges). This can be done by using the suggested calculated column changes above or something similar.

    Having reviewed the requirement again, the logic behind the [Last Visit Last Year] measure that I proposed is slightly different from what is required because it only looks at the last visit date within the previous financial year to the one currently selected in the slicer. On the other hand Gerhard's is looking for the last visit date any time before the first date in the selected financial year and this is more in-line with the actual requirement. With the provided dataset, both approaches provide the same outcome, but the difference in behaviour would become apparent if the last visit date was not found within the previous financial year to the one selected in slicer.



    Tuesday, January 28, 2014 6:51 AM
    Answerer
  • LASTDATE/FIRSTDATE is not appropriate here as it causes an unnecessary context switch 
    for this scenario MIN/MAX is the better option, also in terms of performance

    -gerhard


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

    Tuesday, January 28, 2014 8:10 AM
    Answerer
  • LASTDATE/FIRSTDATE is not appropriate here as it causes an unnecessary context switch 
    for this scenario MIN/MAX is the better option, also in terms of performance

    -gerhard


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


    Fair point :)
    Tuesday, January 28, 2014 9:56 AM
    Answerer
  • Hi,

    First of all thank you for replying and I apologize for the delay in posting back.  Your solution works well and infact get me the exact result.  The reason you are not getting the result is my mistake in the calculated column formula in the calendar table.  The correct formula should be:

    =(MONTH(Calendar[Date])<=3,YEAR(Calendar[Date]),YEAR(Calendar[Date])+1)

    My previous incorrect formula was:

    =(MONTH(Calendar[Date])<=3,YEAR(Calendar[Date]),YEAR(Calendar[Date]+1))

    The moment I carried that correction, I got the expected result.

    Thank you once again.


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

    Saturday, February 01, 2014 6:11 AM
  • Hi,

    Thank you for replying.  I adopted Gerhard's solution.  I really appreciate your effort.


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

    Saturday, February 01, 2014 6:12 AM
  • Hi,

    What is the benefit of marking a the Date column of the calendar table as a "Date Table"?


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

    Saturday, February 01, 2014 6:13 AM
  • most time-intelligence functions only work correctly if you have marked a table as datetable and set the date-column accordingly 

    therefor it is also a best practice that if you have a date-table that you also mark it as "Date Table"


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

    Monday, February 03, 2014 8:55 AM
    Answerer
  • Thank you for clarifying.

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

    Monday, February 03, 2014 9:00 AM