• 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

Oscarr

Member
Hi all

how to make track client lost how many days no active already at Powerbi?



Thanks
 

Attachments

  • sample.xlsx
    9.8 KB · Views: 4
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.
 
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?
 
From what point do you need help? Since, you mentioned PowerBI. I assume you are able to load table to data model?
 
From what point do you need help? Since, you mentioned PowerBI. I assume you are able to load table to data model?

i need to help this, how to make track client lost how many days no active already....
 

Attachments

  • S.PNG
    S.PNG
    8.1 KB · Views: 9
  • ss.jpg
    ss.jpg
    33.9 KB · Views: 9
So what's the logic to arrive at "8-May-2019"?

Do you have DAX measure or Parameter that supplies this value?
 
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
 
.... 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.
 
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

  • Sample_Inactive.zip
    52.6 KB · Views: 3
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

  • P1.jpg
    P1.jpg
    28.7 KB · Views: 7
  • P2.jpg
    P2.jpg
    28.1 KB · Views: 7
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. ;)
 
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
 
The set up already does that. Left slider (start date) has no relevance in calculation. Unless it's greater than Last purchase sale date.
 
Back
Top