cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

 

CustomerIDTransIDTransDatePartnerReceiptReceiptReceived
1AA1234561/1/2018A  
2AA1234571/1/2018B  
3AA1234581/1/2018B12341/1/2018
4AA1234591/2/2018A  
5AA1234601/2/2018A12361/3/2018
6AA1234611/2/2018A  
7AA1234621/3/2018B12381/3/2018
8AA1234631/3/2018B12391/10/2018
9AA1234641/3/2018A  
10AA1234651/4/2018A12411/6/2018
11AA1234661/4/2018A  
12AA1234671/6/2018B  
13AA1234681/6/2018B12441/6/2018
14AA1234691/6/2018A  
15AA1234701/7/2018A12461/6/2018
16AA1234711/7/2018A  
17AA1234721/7/2018B  
18AA1234731/7/2018B12491/11/2018
19AA1234741/7/2018A  
20AA1234751/7/2018A12511/7/2018

 

The end result should look something like this:

DateTransactionsReceiptsReceived
1/1/201831
1/2/201830
1/3/201832
1/4/201820
1/5/201800
1/6/201833
1/7/201861
1/8/201800
1/9/201800
1/10/201801
1/11/201801

 

Any and all help is appreciated!

 

-Josh

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Combine Dates & Count

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png


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

View solution in original post

4 REPLIES 4
Highlighted
Super User IX
Super User IX

Re: Combine Dates & Count

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)

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User V
Super User V

Re: Combine Dates & Count

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png


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

View solution in original post

Highlighted
Frequent Visitor

Re: Combine Dates & Count

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!

Highlighted
Super User V
Super User V

Re: Combine Dates & Count

Hi,

 

What happens when you try this measure

 

=[Transactions]/[Receipts received]


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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors