Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
7 years ago
Solved

DAX formula help

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 NameAssign Date
CHRISTOPHER.PEERS01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
CHRISTOPHER.PEERS01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
CHRISTOPHER.PEERS01/08/2019
CHRISTOPHER.PEERS01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
leighann.worrall01/08/2019
CHRISTOPHER.PEERS01/08/2019
steven.fraser01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
CHRISTOPHER.PEERS01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
CHRISTOPHER.PEERS01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
CHRISTOPHER.PEERS01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
CHRISTOPHER.PEERS01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
CHRISTOPHER.PEERS01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
CHRISTOPHER.PEERS01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
CHRISTOPHER.PEERS01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
CHRISTOPHER.PEERS01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
CHRISTOPHER.PEERS01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
CHRISTOPHER.PEERS01/08/2019
leighann.worrall01/08/2019
rebecca.curran01/08/2019
steven.fraser01/08/2019
ANTHONY.CASSIDY11/14/2018
erica.estevao12/03/2018
ANTHONY.CASSIDY11/14/2018
erica.estevao12/03/2018
ANTHONY.CASSIDY11/14/2018
ANTHONY.CASSIDY11/14/2018
ANTHONY.CASSIDY11/14/2018
ANTHONY.CASSIDY11/14/2018
ANTHONY.CASSIDY11/14/2018
ANTHONY.CASSIDY11/14/2018
ANTHONY.CASSIDY11/14/2018
erica.estevao12/03/2018
erica.estevao12/03/2018
erica.estevao12/03/2018
erica.estevao12/03/2018
erica.estevao12/03/2018
erica.estevao12/03/2018
erica.estevao12/03/2018
ANTHONY.CASSIDY11/14/2018
ANTHONY.CASSIDY11/14/2018
ANTHONY.CASSIDY11/14/2018
ANTHONY.CASSIDY11/14/2018
erica.estevao12/03/2018
erica.estevao12/03/2018
erica.estevao12/03/2018
erica.estevao12/03/2018
ANTHONY.CASSIDY11/14/2018
ANTHONY.CASSIDY11/14/2018
ANTHONY.CASSIDY11/14/2018
ANTHONY.CASSIDY11/14/2018
erica.estevao12/03/2018
erica.estevao12/03/2018
erica.estevao12/03/2018
erica.estevao12/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.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      unnijoy
      Post Prodigy
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        Great