Power BI formula not filtering per date on one operand of the equation

[Anonymous]

I'm working with a dataset that, put simply, tracks people who fall, and I also have another one that tracks many thing including time of stay. They're something like this (I'll include the .pbix file at the bottom of this thread):

With this data, I have to make a measure with the following formula:

FallsFormula = COUNT(Falls[Episode])/SUM(Population[2022])*1000

Now, when I put this over a card visual, the math is right, yearly, the formula's result is 0.52. However, when I put this formula in a line graph and put Incident Date over the X axis, I get numbers that are much lower than I'm actually expecting. 

But what is actually happening is:

1/11559*1000 = 0.087

So the first operand is getting filtered correctly by the month I'm asking for, probably because it's the database that contains the date, but for the second operand that is not the case (And just to clarify, I AM converting the JANUARY, FEBRUARY, etc... parts into a date format [2022/1/1, 2022/2/1, etc...]). Is there a way to make this formula do what I need?

Extra info:

• These datasets are pieces of a larger dataset/amount of Excel files, and some of them have dates that show up twice, so I can't really make a centralized calendar table and make them all have a relationship to it.
• Is there a more efficient way to specify month-only dates when the day doesn't matter? I tried just declaring year and month but it either didn't work or I did something wrong I can't really distinguish.

PBIX file.

Thank y'all for your time.

[RJOttenheim]

Then create a column in both tables: YearMonth

For falls:
YearMonth = YEAR(IncidentDate) * 100 + MONTH(IncidentDate)

For population

I dont understand this table, but you can do it 

Then you can put a relation between the 2 tables and off you go

Then create a relation betweeb

[RJOttenheim]

I dont see any error

Your data:

1 in Jan, 1 in feb, 1 in sept, 1 in nov and 2 in dec

Your population is 11559 in 2022

So your numbers should be 0.087, 0.087, 0.087, 0.087, 0.174 together that makes 0.522

Now if the issue is that you dont see any numbers for March april etc. 

Change your formula to: 

FallsFormula = IF(ISBLANK(COUNT(Falls[Episode])),0,COUNT(Falls[Episode])/SUM(Population[2022])*1000)

[Anonymous]

I understand the formula may not have any technical errors, however, what I'm looking for is a formula that filters per month, because I don't need to use the full year population on the second operand, but rather the population corresponding to the month I'm going to show on my graph.

[TonyZhou1980]

Hi MonkeyWrench,

I think you can create a Date table, 

Date = calendar(date(2022,1,1),date(2022,12,31))

then join date table with Falls and Population(you have to change your Population[Month] from string to date format)

then recreate the visual by putting the date[date] on the X axis. My result is as below for reference.

See if it works on you senarios.

Regards

Tony