Forum Discussion
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 Name Assign Date CHRISTOPHER...
Here is an example file, hope that this helps. I added a date table and then two Dax functions.
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
Here is an example file, hope that this helps. I added a date table and then two Dax functions.
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
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])))
Great