Distinct applications count by month giving incorrect results.

[Anonymous]

Hi Guys,

I have an 'AppData' table with five columns appId, app submitted date, app approved date,fundedamnt and app funded date.I need to calculate the number of applications approved, number of applications funded by month for each year.

Below are the measures I used to calculate the number of funded apps:

Total Apps = DISTINCTCOUNT('AppData'[ApplicationId]) 

Total Funded = CALCULATE([Total Apps],AppData[FundedAmnt] > 0))

Funded = CALCULATE([Total Funded], FILTER(AppData,
AppData[FundedDate] >= MIN(dCalender[DimDateKey]) && AppData[FundedDate] <= MAX(dCalender[DimDateKey])
))

dCalender is another date table to which I am referening the dates from.

Using the above measure 'Funded', I created the below pivot table which is returning incorrect counts for each month. Also, the total count on each column is incorrect as well. 

For April column( 4,995 + 7,786+7,365 + 6,735 = 26,881) but the sum shows 33,604.  Please help me resolve this issue.

[Ashish_Mathur]

Hi,

You should not need the Funded measure at all.  Just drag the Total funded measure in the visual.  Ensure that:

1. The Year and Month in your visual are dragged from the dcalendar table; and
2. There is a relatioship from the FundedDate column of AppData Table to the Date column of your dCalendar table.

Hope this helps.

[Anonymous]

@Ashish_Mathur By relationship between the Funded Date and the date column of the dCalender table, do you mean an actual merge join or anything else?

How should I create a relationship between the two columns from these two tables without actually joining them?

[Anonymous · Accepted solution]

I was able to solve the problem finally. There was a default Many-to-One relationship that Power BI created autmatically between my submitted date and the Date field in the dCalender that was causing the issue.

I removed the link/relationship from the 'Manage relationships' tab and the problem was solved.

Thank you for the sugesstion though. 🙂