Count with conditions

[Tony241]

Hi all,
I am having a data below:

I am creating an automation that send me a report of number of destinations appear in that month.
I would like to know what is the best formula for it with the following conditions:

1. Filtered by month (i.e: january, february…)
   2.: if there are 2 rows with the same destination and same date, it will be counted as 1 (i.e: row 3 and row 4 should be counted as 1).

For example:
January: 5 destinations (omit 1 duplicate row)
February: 1 destination

I have been trying ways that I could think of but it does not work.
Thank you in advance.

[SkrOYC]

For the month, you will need another column called [Month] or similar and calculate it based on the [Date].

Examples:

1. For the number of the month:

MONTH([DATE])

2. For the name of the month:

INDEX(
  LIST(
    "January",
    "February",
    "March",
    "April"... /*You got the idea, make the 12 months*/
  ),
  MONTH([DATE])
)

3. For the name and number (to sort it from Jan to Dec)

INDEX(
  LIST(
    "01-January",
    "02-February",
    "03-March",
    "04-April"... /*You got the idea, make the 12 months*/
  ),
  MONTH([DATE])
)

About counting unique values, there is this simple expression:

COUNT(
  UNIQUE(
    /*INSERT A LIST OF VALUES HERE*/
  )
)

The list will vary from table to table. You could even use SELECT() and it has a built in parameter to filter unique values.

Your count could end like this:

COUNT(
  SELECT(
    TABLENAME[DESTINATION],
    /*SOME CONDITION TO TAKE THE VALUES RELATED TO THE NEW MONTH COLUMN*/,
    0=0 /*A TRUE EXPRESSION TO JUST CONSIDER UNIQUE VALUES*/
  )
)

We can’t help much if we don’t have a full explanation of what you have done and your full table schema

[Tony241]

Hi Oscar,
Thank you for your reply.

1. For clarification, I am having a form for user to input a transportation booking records with fields:
   DATE: Pick from calendar
   DESTINATION: Enum list to choose from:

MIA-BD 11:00
MIA-BD 12:30
MIA-BD 14:00
BD-MIA 11:30
BD-MIA 13:00
BD-MIA 14:30

SERVICE: will be automatically calculated based on a chosen Destination
FOLIO NO.: guest booking ID
I have created 2 virtual columns MONTH: MONTH([DATE]) and YEAR: YEAR([DATE])

So I am creating a bot to send me an email every last day of every month with a report of numbers (COUNT) of destination that we operate that month.
for example:
On 1/1/2022, 2 bookings book for MIA-BD 11:00 and 3 bookings book for BD-MIA 11:30 → 2 destinations in total
on 1/2/2022, 1 booking book for MIA-BD 11:00 and 1 booking book for BD-MIA 11:30 → 2 destinations
Total destinations of January: 4

2. May I know where I put these parts?
   INDEX( LIST( “January”, “February”, “March”, “April”… /You got the idea, make the 12 months/ ), MONTH([DATE]) )

[Steve]

The following is COMPLETELY UNTESTED. Typos and bugs are likely. Use at your own risk.

1. Create a new table named (e.g.) Monthly bookings summary with the following columns:

   1. Date
      • Type: Date
   2. Destination
      • Type: Text
   3. Bookings
      • Type: Date
      • App formula:

        FILTER(
          "Bookings",
          AND(
            ISNOTBLANK([Date]).
            ([Date] = [_THISROW].[Date]).
            OR(
              ISBLANK([_THISROW].[Destination]),
              ([_THISROW].[Destination] = [Destination])
            )
          )
        )
        

   4. _ID
      • Type: Text
      • Key?: Y
      • Label?: Y
      • App formula: TRIM([Date] & " " & [Destination])

2. Create an action named (e.g.) Delete this monthly summary booking:

   • For a record of this table: Monthly bookings summary (from step 1)
   • Do this: Data: Delete this row
   • Prominence: Do not display
   • Needs confirmation: N

3. Create an action named (e.g.) Delete all monthly summary bookings:

   • For a record of this table: Monthly bookings summary (from step 1)
   • Do this: Data: execute an action on a set of rows
   • Referenced Table: Monthly bookings summary (from step 1)
   • Referenced Rows: FILTER("Monthly bookings summary", TRUE)
   • Referenced Action: Delete this monthly summary booking (from step 2)
   • Prominence: Do not display

4. Create an action named (e.g.) Add this booking to monthly summary:

   • For a record of this table: Bookings
   • Do this: Data: add a new row to another table using values from this row
   • Table to add to: Monthly bookings summary (from step 1)
   • Set these columns:
     • Date: [Date]
     • Destination: [Destination]
   • Prominence: Do not display
   • Only if this condition is true:

     AND(
       ISNOTBLANK([Date]),
       ISNOTBLANK([Destination]),
       ISBLANK(
         FILTER(
           "Monthly bookings summary",
           AND(
             ([_THISROW].[Date] = [Date]),
             ([_THISROW].[Destination] = [Destination])
           )
         )
       )
     )
     

5. Create an action named (e.g.) Add all bookings for this monthly summary:

   • For a record of this table: Monthly bookings summary (from step 1)
   • Do this: Data: execute an action on a set of rows
   • Referenced Table: Bookings
   • Referenced Rows:

     FILTER(
       "Bookings",
       (EOMONTH(TODAY(), 0) = EOMONTH([Date], 0))
     )
     

   • Referenced Action: Add this booking to monthly summary (from step 4)
   • Prominence: Do not display

6. Create an action named (e.g.) Add this date to monthly summary:

   • For a record of this table: Monthly booking summary
   • Do this: Data: add a new row to another table using values from this row
   • Table to add to: Monthly bookings summary (from step 1)
   • Set these columns:
     • Date: [Date]
   • Prominence: Do not display
   • Only if this condition is true:

     AND(
       ISNOTBLANK([Date]),
       ISNOTBLANK([Destination]),
       ISBLANK(
         FILTER(
           "Monthly bookings summary",
           AND(
             ([_THISROW].[Date] = [Date]),
             ISBLANK([Destination])
           )
         )
       )
     )
     

7. Create an action named (e.g.) Add all dates for this monthly summary:

   • For a record of this table: Monthly bookings summary (from step 1)
   • Do this: Data: execute an action on a set of rows
   • Referenced Table: Monthly bookings summary (from step 1)
   • Referenced Rows: FILTER("Monthly bookings summary", TRUE)
   • Referenced Action: Add this date to monthly summary (from step 6)
   • Prominence: Do not display

8. Create an action named (e.g.) Add this destination to monthly summary:

   • For a record of this table: Monthly booking summary
   • Do this: Data: add a new row to another table using values from this row
   • Table to add to: Monthly bookings summary (from step 1)
   • Set these columns:
     • Destination: [Destination]
   • Prominence: Do not display
   • Only if this condition is true:

     AND(
       ISNOTBLANK([Date]),
       ISNOTBLANK([Destination]),
       ISBLANK(
         FILTER(
           "Monthly bookings summary",
           AND(
             ISBLANK([Date]),
             ([_THISROW].[Destination] = [Destination])
           )
         )
       )
     )
     

9. Create an action named (e.g.) Add all destinations for this monthly summary:

   • For a record of this table: Monthly bookings summary (from step 1)
   • Do this: Data: execute an action on a set of rows
   • Referenced Table: Monthly bookings summary (from step 1)
   • Referenced Rows: FILTER("Monthly bookings summary", TRUE)
   • Referenced Action: Add this destination to monthly summary (from step 😎
   • Prominence: Do not display

10. Create an action named (e.g.) Prepare monthly bookings summary:

    • For a record of this table: Monthly bookings summary (from step 1)
    • Do this: Grouped: execute a sequence of actions
    • Actions:
      • Delete all monthly summary bookings (from step 3)
      • Add all bookings for this monthly summary (from step 5)
      • Add all dates for this monthly summary (from step 7)
      • Add all destinations for this monthly summary (from step 9)
    • Prominence: Do not display

11. Create a new bot or update an existing bot to perform the Prepare monthly bookings summary action (from step 6) immediately before you generate your monthly summary report.

12. In your report template, include the following:

    <<Start: ORDERBY(FILTER("Monthly bookings summary", ISBLANK([Destination])), [Date])>>
    On <<[Date]>>, <<Start: ORDERBY(FILTER("Monthly bookings summary", ISBLANK([Date])), [Destination])>><<Start: FILTER("Monthly bookings summary", AND(([_THISROW-2].[Date] = [Date]), ([_THISROW-1].[Destination] = [Destination]), ISNOTBLANK([Bookings])))>><<COUNT([Bookings])>> booking(s) for <<[Destination]>>, <<End>><<End>><<COUNT(FILTER("Monthly bookings summary", ISBLANK([Date])))>> destination(s) total<<End>>
    
    Total destinations for <<INDEX(LIST("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"), MONTH(TODAY()))>>: <<COUNT(FILTER("Monthly bookings summary", AND(ISNOTBLANK([Date]), ISNOTBLANK([Destination]))))>>
    

[Aurelien]

we should have a firework icon instead of just a heart for answers like this one

[Suvrutt_Gurjar]

Fully agreed @Aurelien. @Steve magic at work.

[Tony241]

So I am thinking of the way to count number DESTINATION for each date and then sum them up. Is there a way to do so?