cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## How to excluded unique values that counted in the previous month

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 :

CALCULATE(
DISTINCTCOUNT(Table1[ChildrenId]),

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.

Solution Sage

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)),

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.