Forum Discussion

Danielwood's avatar
Danielwood
Helper I
1 year ago

Count months

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 calc...
Need Help
Danielwood's avatar
Danielwood
Helper I
1 year ago

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

Unique IDDelivery
ABC101/10/24
ABC201/11/24
ABC301/11/24
ABC401/10/24
ABC501/02/25
ABC601/10/24
ABC701/02/25
ABC801/02/25
ABC901/10/24

 

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

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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's avatar
    Danielwood
    Helper I
    1 year ago

    how would i create that seperate table please?

  • Danielwood's avatar
    Danielwood
    Helper I
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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.