create a measure using data from two unrelated tables.

[Anonymous]

Hi

I need assistance to create a measure using data from two unrelated tables. Table 1 contains population data by year, 2013 to 2020. Table 2 contains data on the level of injures: “Deaths”, “serious injury” etc these records are by dates. I need a measure to calculate rate = counts of “Death”/population * 100,000. The calculate rate could be for the year as well as by quarter of a year. The population within a year is the same for all quarters of the year.

Here is the data

https://drive.google.com/open?id=1n84BTQ6_kXCuvClFylDW_jSyFFV29Tip

[amitchandak]

@Anonymous , typically the population remains the same in a year.

Create a date column and join it with the common date  table

Date = Date(Year,1,1)

Population should be taken once per year, what month we select

You can try a measure like this

sumx(summarize('Population', Date[year], "_1", calculate(max('Population'[Population]), filter(All(Date), Date[Year] in values(Date[Date])))),[_1])

[Anonymous]

Thanks

I have also calendar table, can we make use of it, if possible pls send me Bi file.

Thanks