Forum Discussion

shankyy7227's avatar
shankyy7227
Frequent Visitor
8 years ago
Solved

Count of Status Change

Dear All,

 

I need help on writing DAX for calculating the count of customers whose status flag is changing as shown in below table in a particular month.

 

1. Total number of distinct Customers for every month with max date.

2. Total number of distinct Customers Pending for every month..

 

CustomerNumberCustomerStatusDate
1Approved8/18/2017
1Pending8/22/2016
2Withdrawn8/18/2017
2Pending8/22/2016
3Unlicensed6/5/2018
4Regular10/25/2016
5Regular4/11/2016
6Approved8/26/2016
6Pending4/21/2016
7Approved6/12/2018
7Unlicensed11/15/2016
8Approved7/28/2016
8Pending11/16/2017
9Pending11/25/2015
10Approved10/2/2015
11Approved5/24/2017
11Closed8/18/2017
11Pending6/26/2017
12Approved10/25/2016
13Unlicensed9/30/2016
14Approved10/11/2016
15Approved2/16/2017
15Closed6/5/2018
15Pending10/11/2016
General Comment
Interesting Links
Need Help
Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi shankyy7227,

     

    1. Total number of distinct Customers for every month with max date.

    Could you please provide more description? What is your desired result?

     

    2. Total number of distinct Customers Pending for every month..

    You could try this measure:

    Measure 2 =
CALCULATE (
    DISTINCTCOUNT ( 'Example Data'[CustomerNumber] ),
    FILTER (
        ALLEXCEPT ( 'Example Data', 'Example Data'[Date].[Month] ),
        'Example Data'[CustomerStatus] = "Pending"
    )
)

    Best regards,

    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi shankyy7227,

     

    1. Total number of distinct Customers for every month with max date.

    Could you please provide more description? What is your desired result?

     

    2. Total number of distinct Customers Pending for every month..

    You could try this measure:

    Measure 2 =
CALCULATE (
    DISTINCTCOUNT ( 'Example Data'[CustomerNumber] ),
    FILTER (
        ALLEXCEPT ( 'Example Data', 'Example Data'[Date].[Month] ),
        'Example Data'[CustomerStatus] = "Pending"
    )
)

    Best regards,

    Yuliana Gu