# Power BI Exchange

View Only

## Calculating tenure

• #### 1.  Calculating tenure

Silver Contributor
Posted Nov 08, 2018 02:47 PM
​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 Nov 08, 2018 05:18 PM
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

Silver Contributor
Posted Nov 08, 2018 05:23 PM

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 Nov 08, 2018 05:51 PM
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

Gold Contributor
Posted Nov 09, 2018 04:01 AM

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

Silver Contributor
Posted Nov 09, 2018 07:55 AM

​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)

training.pbix   519K 1 version

• #### 7.  RE: Calculating tenure Best Answer

Gold Contributor
Posted Nov 09, 2018 09:59 AM
Hi @Malori Meyer

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.

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)

training_reworked.pbix   520K 1 version

• #### 8.  RE: Calculating tenure

Silver Contributor
Posted Nov 09, 2018 10:04 AM
​Beautiful! Number 2 worked! The blank() and isblank() is what I needed! Thank you!

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