Combine Dates & Count

[jfunderburk]

Hello!

I am trying to take a dataset with dates and id numbers for transcations and summarize in to table that count the transcations and receipts that occured on a particular day. Essentialy the issue I am running in to is the dates need to be combined into to one and the distinct count of ids needs to show in a table. The catch is that slicers from the base table still need to be able to apply to this dataset. (In the example set I provided, I would slice by partner in the report if that is what I needed to do).

Here is a mock of what the columns coming in that are applicable to my question look lilke.

CustomerID	TransID	TransDate	Partner	Receipt	ReceiptReceived
1	AA123456	1/1/2018	A
2	AA123457	1/1/2018	B
3	AA123458	1/1/2018	B	1234	1/1/2018
4	AA123459	1/2/2018	A
5	AA123460	1/2/2018	A	1236	1/3/2018
6	AA123461	1/2/2018	A
7	AA123462	1/3/2018	B	1238	1/3/2018
8	AA123463	1/3/2018	B	1239	1/10/2018
9	AA123464	1/3/2018	A
10	AA123465	1/4/2018	A	1241	1/6/2018
11	AA123466	1/4/2018	A
12	AA123467	1/6/2018	B
13	AA123468	1/6/2018	B	1244	1/6/2018
14	AA123469	1/6/2018	A
15	AA123470	1/7/2018	A	1246	1/6/2018
16	AA123471	1/7/2018	A
17	AA123472	1/7/2018	B
18	AA123473	1/7/2018	B	1249	1/11/2018
19	AA123474	1/7/2018	A
20	AA123475	1/7/2018	A	1251	1/7/2018

The end result should look something like this:

Date	Transactions	ReceiptsReceived
1/1/2018	3	1
1/2/2018	3	0
1/3/2018	3	2
1/4/2018	2	0
1/5/2018	0	0
1/6/2018	3	3
1/7/2018	6	1
1/8/2018	0	0
1/9/2018	0	0
1/10/2018	0	1
1/11/2018	0	1

Any and all help is appreciated!

-Josh

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution here.

Hope this helps.

[jfunderburk]

Both great responses! I went with Ashish's version as it allowed the total columns to work.

A follow up question.. how can I create a measure to divide the count of the transactions by the receipts? I guess the UseRelationship functions are giving me trouble when I try to do so. The error said that column cannot be found or used in this calculation.

Thanks!

[Ashish_Mathur]

Hi,

What happens when you try this measure

=[Transactions]/[Receipts received]

[Greg_Deckler]

I believe that you want measures like this:

Transactions = VAR trans = DISTINCTCOUNT([CustomerID])
RETURN IF(ISBLANK(trans),0,trans)

ReceiptsReceived = VAR receipts = CALCULATE(COUNT('#Receipts'[ReceiptReceived]),FILTER(ALL('#Receipts'),'#Receipts'[ReceiptReceived]=MAX('#Receipts'[TransDate])))
RETURN IF(ISBLANK(receipts),0,receipts)