Sumif one condition and between two dates

[Anonymous]

How to count in a table A, the associated values ​​in a table B according to a condition and between two dates

[v-yiruan-msft]

Hi @Anonymous ，

Do you want to calculate the number of customers in the date range of tables A and B?Is there any relationship between these two tables?

Best Regards

Rena

[Anonymous]

Hi @v-yiruan-msft
Thank you very much for your contributions and help to the whole community.
What I really need is a union of my two publications yesterday, I will try to summarize:
As you know I have a goal table, it's called FO15-ANS Visita a clientes ents and another transactional table called FO15-Visita Cliente.

The goals change month by month so the goal table has a Fecha column where the date of the month (first day of the month) is loaded. This table is repeated for the following month and the Fecha column changes to the first day of the following month. In the transactional table there are all the visits and it has a selected date column FechaVis, so we create a selected FO15-Fechas table that helps us to relate both tables.

The ANS table has a selected Frecuencia column, which the sum of this column in month X gives us the total of the month's goal

Now, between both tables there is another relationship through a column called CodigoProyecto that is related by another selected FO15-DIM_Proyecto table.

This helps us compare what was done against the goal month by month using the filter options.
In order to carry out the control where the views carried out do not exceed the goal, we create a measure that helps us to count on Projects and individually assesses the data, this measure is called Performed Max. This measure is a conditional:

Realizadas Max = IF ( DISTINCTCOUNT('FO15-Visita Cliente'[prikey]) > sum('FO15-ANS Visita a clientes'[Frecuencia]) ; sum('FO15-ANS Visita a clientes'[Frecuencia]) ; [Realizadas Meta])

However, when we need the final data of all of the visits, it's showing me all of the visits, even those that exceed the goals..

So, what I need is a measure that helps me count only the visits made, but not does ones where the individual goal is exceede

I leave the model in PBIX. 

https://1drv.ms/u/s!ArJzwBid6CN3xZtQwMskCbBah1SqJA?e=IiSVDn 

Thank you very much for your help

[v-yiruan-msft]

Hi @Anonymous ，

Thanks for providing information, I'm checking it. Any progress I will update you in time. Thank you.

Best Regards

Rena

[Anonymous]

Hi @v-yiruan-msft, i just wanted to know if you've been able to work on a solution for me, if you need more info please let me know. Looking forward to hear from you.

[v-yiruan-msft]

Hi @Anonymous ，

Thanks for providing information. According to your description, it seems the value of measure Realizadas Max didn't display correctly. The correct calculation is if the number of visits is greater than goal number, it display goal number. Otherwise, it display actual number of visits. Here I'm not clear about this sentence:
what I need is a measure that helps me count only the visits made, but not does ones where the individual goal is exceeded.

What “individual goal” in above sentence means? What is based on?

Best Regards

Rena

[Ashish_Mathur]

Hi,

Ideally you should build a Calendar Table and create a relationship from the Date column of your base data table to the Date column of your Calendar Table.  In the Calendar Table, write these calculated column formulas to extract Year and Month

Year = YEAR(Calendar[Date])

Month Name = FORMAT(Calendar[Date],"mmmm")

Month Number = MONTH(Calendar[Date])

Click on any cell in the Month Name column and go to Sort by Column > Month Number

To your visual, drag Year and Month from the Calendar Table and Vendedor from the base data table.  Write this measure

Count = COUNTROWS(Data)

Hope this helps.