Count months

[Danielwood]

Hi,

In my table I have unique records, each with a target delivery date. I am trying to create a scatter graph which shows the number of records with a target delivery each month. Is there a calculation to distinct count each unique record and total by month? When I do a measure to count deliveries by month, it shows too many responses.

I have probably explained that badly. But I think what I need is a calculation that counts all deliveries expected per month, where the unique record status is Active.

As a follow on, is there then a way in a scatter graph using month as the 

[rajendraongole1]

Hi @Danielwood - if you are looking for count of deliveries with month you can try with distinct count function to get the unquie records with a target delivery date by month

eg:

DistinctCountActiveDeliveriesByMonth =
CALCULATE(
DISTINCTCOUNT('YourTable'[UniqueRecordID]),
'YourTable'[Status] = "Active"
)

Hope it works, if any please share with some sample data to work further.

[Danielwood]

possibly i haven't entered it corrently, but the table would look like this

Unique ID	Delivery
ABC1	01/10/24
ABC2	01/11/24
ABC3	01/11/24
ABC4	01/10/24
ABC5	01/02/25
ABC6	01/10/24
ABC7	01/02/25
ABC8	01/02/25
ABC9	01/10/24

So in this example on my graph i would be expecting 4 in October, 2 November, then 3 Feb 2025

[rajendraongole1]

Hi @Danielwood - as per shared information, 

can you check below steps

I have created a relationship with Date table with uniq table (Delivery date to Date column Date table)

create a measure that will count the unique Uniqueid records for each month.

output as per shared data in scatterplot chart

Hope it helps.

[Danielwood]

I managed to follow the instructions to create the table but i get an error

"The expression refers to multipl columns. Multiple columns cannot be converted to a scalar value."

[Anonymous]

Hi,rajendraongole1 .

thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello,@Danielwood .I am glad to help you.
Based on your description, you seem to be having a problem creating a calculation table.
When you need to create a calculation table, you need to create a new calculation table by clicking on New Tables
This will create it properly.

Here is my reproduction of the code provided by rajendraongole1, hope it helps.

DateTableU = 
 ADDCOLUMNS(
    CALENDAR(MIN('uniqD'[Delivery]),MAX('uniqD'[Delivery])),
    "Year",YEAR([Date]),
    "Month",MONTH([Date]),
    "MonthName",FORMAT([Date],"MMMM")
 )

Unique Deliveries Per Month = 
CALCULATE(
    DISTINCTCOUNT('uniqD'[Unique ID]),
    ALLEXCEPT('DateTableU','DateTableU'[Year],'DateTableU'[Month])
)

If you think what rajendraongole1 suggests is helpful, you can mark his suggestion as a solution, which will help more users in the forum who have similar problems.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Danielwood]

how would i create that seperate table please?