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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.