How to Count (qty of leads) daily?

[Anonymous]

Hi there,

I'm trying to calculate how many leads are being created on a daily basis.

On the table view_leads a created a column called DAY/DATE with the following formula: DAY/DATE = FORMAT(view_leads[create_timestamp],"DD/MMM/YYYY")

I have a second table where I'm summarizing all the calculations and there I created a column called Qty of Leads with the following formula:  Qty of Leads = CALCULATE(COUNT(view_leads[id]),ALLEXCEPT(view_leads, view_leads[DAY/DATE]))

The result is that is that the calculation is returning the Year to Date counting on every day/date.

Any ideas on how to fix it?

[VahidDM · Accepted solution]

Hi @Anonymous 

Try this:

Qty of Leads =
CALCULATE (
    COUNT ( view_leads[id] ),
    FILTER ( view_leads, view_leads[Day/Date] = [Date] )
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Anonymous]

I edited the format and now it has worked. Is there any way to replace the blank values to zero in that formula?

Thanks again!

[VahidDM]

HI @Anonymous 

Try this:

Qty of Leads =
Var _C =
CALCULATE (
    COUNT ( view_leads[id] ),
    FILTER ( view_leads, view_leads[Day/Date] = [Date] )
)
return
if(isblank(_C),0,_C)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Anonymous]

It didn't work, it returns an error message: DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

[samdthompson]

Ok, its the relationship from the view_leads back to the Y Total Calculations path. Its not going to work like that. You really need to connect the DateDimension table direct to the view leads. rather than via that Month Table. its not going to work otherwise. *:1 Month:Date and 1:* Month:Leasds. The solution after fixing the relationships is just make a measure in the view_leads table:

_Qty of Leads = CALCULATE(COUNTDISTINCT(view_leads[id]),ALLEXCEPT(DateDimension, DateDimension[DAY/DATE]))

[Anonymous]

When I changed the relationship it messed with my visuals in other tab. Do you believe I can do that in other table?

[Anonymous]

That's the problem, I need to create that count in the table Y Total Calculations. I'm wondering if I mess with the relationship, other visuals may be affected too. 

[samdthompson]

Hi, can you share a picture of the relationship diagram. I suspect its becasue you are making the calculated column on the date table and there will be a oneway relationship from the date table to the fact table.

You could try to make this same calculated column on the fact table with:

Qty of Leads = CALCULATE(COUNTDISTINCT(view_leads[id]),ALLEXCEPT(calendar, calendar[DAY/DATE]))

[Anonymous]

There you go. It's a 1to1 relationship.