• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Powerbi Track lost client

Chihiro

Excel Ninja
Load to data model. Add DAX measure.

Code:
Inactive (days) = YourDateValue - CALCULATE(MAX([Date]),ALLEXCEPT(TableName,TableName[Name]))
Above is with assumption, that only Name column is used for evaluation context.

If other filters etc are applied. You'll need to alter DAX to accommodate.
 

Oscarr

Member
Load to data model. Add DAX measure.

Code:
Inactive (days) = YourDateValue - CALCULATE(MAX([Date]),ALLEXCEPT(TableName,TableName[Name]))
Above is with assumption, that only Name column is used for evaluation context.

If other filters etc are applied. You'll need to alter DAX to accommodate.
hi @Chihiro

Thanks for reply....but i don understand how to make it, can you make a sample for me?
 

Chihiro

Excel Ninja
From what point do you need help? Since, you mentioned PowerBI. I assume you are able to load table to data model?
 

Chihiro

Excel Ninja
So what's the logic to arrive at "8-May-2019"?

Do you have DAX measure or Parameter that supplies this value?
 

Oscarr

Member
So what's the logic to arrive at "8-May-2019"?

Do you have DAX measure or Parameter that supplies this value?
8-May-2019 is when i adjust the date 1-APR-2019 to 8-May-2019, then show me all client between this date how days no active already..

(Do you have DAX measure or Parameter that supplies this value?), this i not so sure how to make
 

Chihiro

Excel Ninja
.... so where are you getting that 8-May-2019 from? Manually selected from slicer? If so, what field/table supplies values to that Slicer?

Without knowing your model, it is bit hard for me to give you precise help.
 

Chihiro

Excel Ninja
Come to think of it, if doing it in PowerBI and not in Excel data model. DAX should be changed to... (it's also better to use below in Excel as well)
Code:
Inactive (Days) = DATEDIFF(CALCULATE(MAX(Table1[Date]),ALLEXCEPT(Table1,Table1[Name])),[MaxSelectedDate],DAY)
See attached sample. However, I assume your actual data is structured differently. I'd recommend uploading sample raw data structure to get more precise help.
 

Attachments

Oscarr

Member
Come to think of it, if doing it in PowerBI and not in Excel data model. DAX should be changed to... (it's also better to use below in Excel as well)
Code:
Inactive (Days) = DATEDIFF(CALCULATE(MAX(Table1[Date]),ALLEXCEPT(Table1,Table1[Name])),[MaxSelectedDate],DAY)
See attached sample. However, I assume your actual data is structured differently. I'd recommend uploading sample raw data structure to get more precise help.
Thanks for your help....but got some problem...

i drag the cursor from start date to middle, and it doesnt count automactically. but if i drag the cursor from end to middle and it works..

It is able to do it both side?
 

Attachments

Chihiro

Excel Ninja
Well, 1/1/2019 is "Before" your date field. And the measure is calculating date difference between MAX selected slicer value (12/31/2019) and the date stored in the field.

You'll need to explain to me in detail what your exact requirements are. After all I'm not a mind reader. ;)
 

Oscarr

Member
Well, 1/1/2019 is "Before" your date field. And the measure is calculating date difference between MAX selected slicer value (12/31/2019) and the date stored in the field.

You'll need to explain to me in detail what your exact requirements are. After all I'm not a mind reader. ;)
hi @Chihiro

i exact both side date when i adjust the date also can calculating...

Example1:jack active date is 15-3-2019 to 20-3-2019, when i adjust start date to 1-3-2019 and end date is 31-3-2019, then auto show me jack
data is 11 days not active, because 21-3-2019 to 31-3-2019 he start not active....

Example2:jack active date is 15-3-2019 to 20-3-2019, when i adjust start date to 25-3-2019 and end date is 31-3-2019, then auto show me jack
data is 7 days not active, because i adjust the date is start from 25-3-2019, so calculating start from 25-3-2019 to 31-3-2019 he start not active....

Example3:jack active date is 15-3-2019 to 20-3-2019,start date is 1-3-2019, i adjust end date to 26-3-2019, then auto show me jack
data is 6 days not active, because i adjust end date is 26-3-2019, so calculating jack not active days is 21-3-2019 to 26-3-2019 stop...

Thanks
 

Chihiro

Excel Ninja
The set up already does that. Left slider (start date) has no relevance in calculation. Unless it's greater than Last purchase sale date.
 
Top