Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

New,Lost,Existing Customer Count -- Dax Help

Hello, I just want to count new,lost,existing customer. Below are my data and scenario Emp Name EMP iD Date Month A 101 4/30/2020 Apr B 102 4/30/2020 Apr C 103 4/30/2020 Apr...
Need Help
Tips and Tricks
  • Fowmy's avatar
    Fowmy
    6 years ago

    Anonymous 

    I have worked out a solution that you can find in the attached PBIX file. Hope it answers your questions.
    I added a calendar table as well. Modify your table and field names as necessary.



    Download:  https://1drv.ms/u/s!AmoScH5srsIYgYIRa-ntfQ0YKA50IQ?e=Pmq6KZ

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    I accept KUDOS 🙂

    YouTube, LinkedIn

     

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
6 years ago

Anonymous 

I have worked out a solution that you can find in the attached PBIX file. Hope it answers your questions.
I added a calendar table as well. Modify your table and field names as necessary.



Download:  https://1drv.ms/u/s!AmoScH5srsIYgYIRa-ntfQ0YKA50IQ?e=Pmq6KZ

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Fowmy 
    Thanks for help fowmy..
    I was calculating Existing customer and received your answer..
    Below are my approach.

    Existing : 

    VAR _1 = CALCULATETABLE(VALUES(Source[Emp Name]))
    VAR _2 = CALCULATETABLE(VALUES(Source[Emp Name]),FILTER(ALL(Source),Source[Month Number] = MAX(Source[Month Number])-1 ) )
    VAR _common=INTERSECT(_1,_2)
    return
    COUNTROWS(_common)

    Lost:
    VAR _1 = CALCULATETABLE(VALUES(Source[Emp Name]))
    VAR _2 = CALCULATETABLE(VALUES(Source[Emp Name]),FILTER(ALL(Source),Source[Month Number] = MAX(Source[Month Number])-1 ) )
    VAR _common=EXCEPT(_2,_1)
    return
    COUNTROWS(_common)

    New:
    VAR _1 = CALCULATETABLE(VALUES(Source[Emp Name]))
    VAR _2 = CALCULATETABLE(VALUES(Source[Emp Name]),FILTER(ALL(Source),Source[Month Number] = MAX(Source[Month Number])-1 ) )
    VAR _common=EXCEPT(_1,_2)
    return
    COUNTROWS(_common)

    I have not executed your solution yet.. Let me execute first..If i have any question..i will let you know obvious..
    thanks