Power BI Exchange

Expand all | Collapse all

Calculating tenure

Jump to Best Answer
  • 1.  Calculating tenure

    Posted 10 days ago
    ​Hello,

    I have calculated tenure as :
    Tenure Days =
         (LASTDATE(Sales[Purchase Date])-FIRSTDATE(Sales[Purchase Date]))*1

    It works fine but shows zero for customers that show up only once. I would like it to show a zero to make it be useful in other calculations.

    I tried an IF statement, while it works, its removing the possibility of slicers on the table. meaning if I want to see tenure by region. the region slicer does not work on the tenure table because all customers have a calculation. What would be the solution to this?

    Tenure Days =
    IF( (LASTDATE(Sales[Purchase Date])-FIRSTDATE(Sales[Purchase Date]))*1 = 0,
         1,
         (LASTDATE(Sales[Purchase Date])-FIRSTDATE(Sales[Purchase Date]))*1)

    ------------------------------
    Malori Meyer
    Analyst
    9798249358
    ------------------------------


  • 2.  RE: Calculating tenure

    Top Contributor
    Posted 10 days ago
    Hi,

    Try using following;

    Tenure Days = IF(LASTDATE(Sales[Purchase Date]) = FIRSTDATE(Sales[Purchase Date]), 1, LASTDATE(Sales[Purchase Date]) - FIRSTDATE(Sales[Purchase Date]))* 1

    When your Last (Sales[Purchase Date]) = First (Sales[Purchase Date]) means no other sale so it will show [1] as default, otherwise provide Last Day - First Day

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Sr. BI Consultant
    Karachi, Pakistan
    ------------------------------



  • 3.  RE: Calculating tenure

    Posted 10 days ago
    example
    This does not work either.
    Notice that now every sales person is brought in overriding the slicer. While first sales date and last sales date are still calculating correctly (only for the slicer).

    ------------------------------
    Malori Meyer
    Analyst
    9798249358
    ------------------------------



  • 4.  RE: Calculating tenure

    Top Contributor
    Posted 10 days ago
    Hi,

    You didn't want to put filter on visual level to filter out rows where tenure = 1

    Please elaborate what end goal you are trying to achieve

    ------------------------------
    Hasham Bin Niaz
    Sr. BI Consultant
    Karachi, Pakistan
    ------------------------------



  • 5.  RE: Calculating tenure

    Bronze Contributor
    Posted 9 days ago

    Hi @Malori Meyer  is it possible for you to share sample data to understand better your requirements? I am a bit confused by your sentence:

    QUOTE

    It works fine but shows zero for customers that show up only once. I would like it to show a zero to make it be useful in other calculations.

    UNQUOTE

    In the first part you are stating that your current measure is ok but giving zero. Then in the 2nd part you are stating that you want to show the zero. Isn't it contradictory?

    1.       Are you looking to see how many times a customer shows up? And in case he shows up only once, you want to mark that as 0? And in case a customer does not show up, then his name should not come up in the visual?

    2.       Or are you looking at the first sale date and the last sale date and finding the days between these?

    The solution will vary based on what exactly you are trying to achieve. If it is the former, then a DAX which counts the rows may be the way or if it is the latter, then a DAX which gets the days between the first date and the last date might be the way.

    I apologize if I have misunderstood your query.



    ------------------------------
    Gopa Kumar S

    ------------------------------



  • 6.  RE: Calculating tenure

    Posted 9 days ago
      |   view attached

    ​Yes, sorry. I mistyped. I do not want to see any zeros, but I want a slicer to still work with the table. Here is the powerBI file. I have been working along with Enterprise DNA on youtube and are now branching out on my own. the tab would be called "Top Sales People Per Region". You will see that the calculation is not exactly calculating correctly because it is inserting a 1 for every sales person... which is overriding the slicers.



    ------------------------------
    Malori Meyer
    Analyst
    9798249358
    ------------------------------

    Attachment(s)

    pbix
    training.pbix   519K 1 version


  • 7.  RE: Calculating tenure
    Best Answer

    Bronze Contributor
    Posted 9 days ago
      |   view attached
    Hi @Malori Meyer

    Please see if the following solves your issue.

    I have made two measures:

    1. [New Tenure Days] which puts 0 if the first sale and last sale is the same date;

    New Tenure Days = 
    VAR firstsale = [First Sales Date]
    VAR lastsale = [Last Sales Date]
    VAR tenure =
        DATEDIFF ( firstsale, lastsale, DAY )
    RETURN
      tenure


    2. [New2 Tenure Days] which puts 1 if the first sale and last sale is the same date

    New2 Tenure Days = 
    VAR firstsale = [First Sales Date]
    VAR lastsale = [Last Sales Date]
    VAR tenure =
        DATEDIFF ( firstsale, lastsale, DAY )
    RETURN
        SWITCH ( TRUE (), ISBLANK ( tenure ), BLANK (), tenure > 0, tenure, 1 )

    The resulting table is as below, which as you can see, respects the filters. I am also enclosing the re-worked pbix file for your reference.

    thumbnail image

    The above is based on my understanding of what your issue was. I apologize if I have misunderstood your issue.



    ------------------------------
    Gopa Kumar S

    ------------------------------

    Attachment(s)

    pbix
    training_reworked.pbix   520K 1 version


  • 8.  RE: Calculating tenure

    Posted 9 days ago
    ​Beautiful! Number 2 worked! The blank() and isblank() is what I needed! Thank you!

    ------------------------------
    Malori Meyer
    Analyst
    9798249358
    ------------------------------