Summarize Count per Day

[markefrody]

Hi,

I have a table which looks like this:

pbix file can be found here:
https://www.dropbox.com/s/ohxiqo3wsughxeo/Sample%20Data.pbix?dl=0

I'm trying to create a DAX code with these two conditions if I filter for field "DatePlaced":
1. # Loaded = Count all fields which are "blank" in the "DateEmptied" column.
2. # Empty = Count all fields which are not "blank" in the "DateEmptied" column.

It should appear like this:

Then summarize it per "DatePlaced" like this. Should be summarized by date only. No time needed:

Appreciate your kind assistance. Thanks!

Best regards,
Mark V.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[markefrody]

Thanks @Ashish_Mathur! This is what I was looking for. Thank you for your assistance.

[Ashish_Mathur]

You are welcome.

[amitchandak]

@markefrody ,

Create a separate date column in power query or dax and try to use that

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

Power query

DateTime.Date([datetime])

[markefrody]

Hi @amitchandak, 

I already have this as a date column named "DatePlaced(DateOnly)".

Will you be able to assist me in creating the DAX code for the 2 conditions and summary? Thanks.

[HotChilli]

#loaded = COUNTROWS(FILTER(Sheet1, Sheet1[DateEmptied] = BLANK()))

#empty should just be a simple count - I'll let you do that one.

I don't get quite the same results shown in the table, e.g. 11/17 has 8 empty, 11/18 is in the table twice with different results. Can you check please?

[markefrody]

@HotChilli that you for your reply and assistance. I really appreciate it.
Sorry for the excel table mistake. Was in a rush to post it. You are right 11/17 has 8 empty. Thanks.