DAX formula help

[unnijoy]

Hi,

I have usernames in Col A and Date of plan Assign in column B. I need a formula that help me the disting count of useres added till up to each month.

User Name	Assign Date
CHRISTOPHER.PEERS	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
leighann.worrall	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
steven.fraser	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
CHRISTOPHER.PEERS	01/08/2019
leighann.worrall	01/08/2019
rebecca.curran	01/08/2019
steven.fraser	01/08/2019
ANTHONY.CASSIDY	11/14/2018
erica.estevao	12/03/2018
ANTHONY.CASSIDY	11/14/2018
erica.estevao	12/03/2018
ANTHONY.CASSIDY	11/14/2018
ANTHONY.CASSIDY	11/14/2018
ANTHONY.CASSIDY	11/14/2018
ANTHONY.CASSIDY	11/14/2018
ANTHONY.CASSIDY	11/14/2018
ANTHONY.CASSIDY	11/14/2018
ANTHONY.CASSIDY	11/14/2018
erica.estevao	12/03/2018
erica.estevao	12/03/2018
erica.estevao	12/03/2018
erica.estevao	12/03/2018
erica.estevao	12/03/2018
erica.estevao	12/03/2018
erica.estevao	12/03/2018
ANTHONY.CASSIDY	11/14/2018
ANTHONY.CASSIDY	11/14/2018
ANTHONY.CASSIDY	11/14/2018
ANTHONY.CASSIDY	11/14/2018
erica.estevao	12/03/2018
erica.estevao	12/03/2018
erica.estevao	12/03/2018
erica.estevao	12/03/2018
ANTHONY.CASSIDY	11/14/2018
ANTHONY.CASSIDY	11/14/2018
ANTHONY.CASSIDY	11/14/2018
ANTHONY.CASSIDY	11/14/2018
erica.estevao	12/03/2018
erica.estevao	12/03/2018
erica.estevao	12/03/2018
erica.estevao	12/03/2018

From the above example i will take Assign date as my X Axis. so it will be Nov 2018, Dec 2018, Jan 2019.

in graph i need to get for Nov 2018 the distint count of usernaes assigned for Nov 2018. Then for Dec 2018 i need to get the distint count of users added from Nov 2018 to Dec 2018. And this should continue for every upcoming month. Means for Jan it should get the disintg count from Nov 2018 to Jan 2019.

Please help me with this.

[Anonymous · Accepted solution]

Here is an example file, hope that this helps. I added a date table and then two Dax functions.

DistUsers

Excel Table

Distinct UserNames = DISTINCTCOUNT(Table2[User Name])

Acc. Distinct UserName = 
VAR minDate = CALCULATE(MIN(Dates[Date]);ALLSELECTED(Dates))
VAR maxDate = MAX(Dates[Date])
VAR distMonth = 
    SUMMARIZE(
        FILTER(
            ALL(Dates);
            Dates[Date] >= minDate && Dates[Date] <= maxDate
        );
        Dates[Year-Month];
        "DistCount";[Distinct UserNames]
    )
RETURN
SUMX(
    distMonth;
    [DistCount]
)

Regards,

Kristjan76

[unnijoy]

Hi @Anonymous  thanks for your help.  I got the solutuion by using the below formula.

Total = CALCULATE(DISTINCTCOUNT(Table[USERNAME]),FILTER(ALLSELECTED(Table),Table[Learning Plan Assigned Date]<=MAX(Table[Learning Plan Assigned Date])))

[Anonymous]

Great