Forum Discussion

markefrody's avatar
markefrody
Post Patron
4 years ago
Solved

Summarize Count per Day

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.

 

Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      markefrody
      Post Patron
      4 years ago

      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's avatar
        HotChilli
        Community Champion
        4 years ago
        #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?