Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mohan_g_das1986
Helper III
Helper III

Create Distinct count on partner Id for 2 dates and show the difference between them

Hi,

 

First question - I have a table with Partner names and Partner id on the date level (2 dates - Yesterday and today), now I have to show my visual as a distinct count of partners for each day; also today's change in these days (from yesterday to today what is the count change?).

 

mohan_g_das1986_0-1654177744957.png

 

What I want is below

 

 Refresh date  
 5/31/20226/1/2022Total Change from Yesterday
RegionCommercialRetailCommercialRetailCommercialRetail
North America5143-1(+2)
Asia3711(+2)-6

 

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Create a Data Table and build a relationship (Many to One and Single) from the refresh Date column of the Data Table to the Date column of the Calendar Table.  Create a slicer from the Date column of the Calendar Table and select June 1, 2022 in the slicer.  Write this measure

Partner count = distinctcount(Data[Partner ID])

Partner count yesterday = calculate([partner count],previousday(calendar[date]))

Variance = [Partner count]-[Partner count yesterday]

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi - Thanks Ashish for the help, DAX formula really helps to achieve what is needed.

I have got the Variance like the below but I need to show which are the partners was there and new today

Example - On the below table Variance is +1 means new partner came in today, when i click on 1 i should show them partner details.. ANY HELP on that?

 

 

mohan_g_das1986_0-1654527668160.png

 

Hi,

Share the download link of your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

How attach or share the pbix file to you

lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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