Skip to main content
cancel
Showing results for 
Search instead 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

Reply
TaufikMaggangka
Helper II
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.

 

ChildrenIdDateStatus
11 january 2020Delete
22 january 2020Delete
33 january 2020Add
43 january 2020Add
11 february 2020Delete
21 february 2020Delete
31 february 2020Delete
42 february 2020Add


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.

1 REPLY 1
SanketBhagwat
Solution Sage
Solution Sage

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.
 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.