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

[mohan_g_das1986]

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?).

What I want is below

	Refresh date
	5/31/2022	6/1/2022	Total Change from Yesterday
Region	Commercial	Retail	Commercial	Retail	Commercial	Retail
North America	5	1	4	3	-1	(+2)
Asia	3	7	1	1	(+2)	-6

[Ashish_Mathur]

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.

[mohan_g_das1986]

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?

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[mohan_g_das1986]

How attach or share the pbix file to you

[lbendlin]

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