Forum Discussion

Danielwood's avatar
Danielwood
Helper I
2 years 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
2 years 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
2 years 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.