Forum Discussion
Combine Dates & Count
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
4 Replies
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)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!
Hi,
What happens when you try this measure
=[Transactions]/[Receipts received]
