How to excluded unique values that counted in the previous month

[TaufikMaggangka]

Hi All,

Kindly need to resolved my issue.

Basically, I want to count the unique values by monthly and excluding the childrenId that counted in the previous month and also I need the total of the number of children id for add and delete status. here my illustration table.

ChildrenId	Date	Status
1	1 january 2020	Delete
2	2 january 2020	Delete
3	3 january 2020	Add
4	3 january 2020	Add
1	1 february 2020	Delete
2	1 february 2020	Delete
3	1 february 2020	Delete
4	2 february 2020	Add

For the total add and delete children id, I though I already correct number used "distinctcount" function and filtered by status.Please see the dax that I used :

Add Children ID = 

CALCULATE(

DISTINCTCOUNT(Table1[ChildrenId]),

FILTER(Table1, Table1[Status] = "Add"))

Add Children ID = 

CALCULATE(

DISTINCTCOUNT(Table1[ChildrenId]),

FILTER(Table1, Table1[Status] = "Delete"))

but the problem is when I try to create "Monthly Chart", distinct count function also counting ChildrenId that already count in the previous month. 

Is it there any way or dax that can be used that can exclude the ChildrenId that already count in the previous month ??

Any help from expert will be really appreciated.

[SanketBhagwat]

Hi @TaufikMaggangka .

Use the below DAX formulaes;

1)

Unique Children = var excludeLastMonth=CALCULATE(MONTH(MAX(Sheet1[Date].[Date])),ALL(Sheet1[ChildrenId]))

return

CALCULATE(

DISTINCTCOUNT(Sheet1[ChildrenId]),

FILTER(Sheet1,

MONTH(Sheet1[Date])<DATE(YEAR(Sheet1[Date].[Date]),MONTH(Sheet1[Date].[Date]),1))).

2)

Unique Children for Add Status= var excludeLastMonth=CALCULATE(MONTH(MAX(Sheet1[Date].[Date])),ALL(Sheet1[ChildrenId]))

return

CALCULATE(

DISTINCTCOUNT(Sheet1[ChildrenId]),

FILTER(Sheet1,

MONTH(Sheet1[Date])<DATE(YEAR(Sheet1[Date].[Date]),MONTH(Sheet1[Date].[Date]),1)),

FILTER(Sheet1,Sheet1[Status]="Add"))

3)

Unique Children for Add Status= var excludeLastMonth=CALCULATE(MONTH(MAX(Sheet1[Date].[Date])),ALL(Sheet1[ChildrenId]))

return

CALCULATE(

DISTINCTCOUNT(Sheet1[ChildrenId]),

FILTER(Sheet1,

MONTH(Sheet1[Date])<DATE(YEAR(Sheet1[Date].[Date]),MONTH(Sheet1[Date].[Date]),1)),

FILTER(Sheet1,Sheet1[Status]="Delete")).

Please let me know if that works.

Regards,

Sanket Bhagwat.

If this post helps, then please mark it as 'Accept as Solution' and give it a big thumbs up.