sum values within a date range in two columns filtered by person id number across two tables

[KPI]

Hi,

I'm after a DAX formula. I have two tables in Power BI Desktop. Table1 and Table2. Table1 has three columns called Hours (integer) and PersonID (integer) and Date (Date). Table2 has two columns called PersonID (integer) and Date of Incident (Date). I'd like to create a new column in Table2 called 'Hours between 3 - 6 Prio to Incident' that sums by each PersonID row in Table2 the hours from Table1 filtered by PersonID in Table1 and Table2 and filtered by date range -3 months prior to Incident to -6 months prior to Incident in Table2?

 

Thanks so much in advance.

[Anonymous · Accepted solution]

@KPI

I think I have a solution for you. It won't yield the same result as you, because 25-02-2017 is more than 6 months away from 01-09-2017.

First you make a calculated column in table1 like this:

Date of incident = LOOKUPVALUE(Table2[Date of Incident];Table2[PersonID];Table1[PersonID])

Then you make another calculated column like this: hours between 3-6 months prior to incident = IF(Table1[Date]<Table1[Date of incident]-93;IF(Table1[Date]>Table1[Date of incident]-182,5;Table1[Hours];0);0)

Then you can plot PersonID from table2 and hours between 3-6 months prior to incident from table1 and get your desired result. For some reason the dates were extremely buggy, which is why i went with the -93 and -182,5 method. There is definitly better (more dynamical) ways to do this, my Power BI is just bugging right now.

Let me know how it goes.

[KPI]

Thank you so much. It worked! Despite Table2 Employee Number not having a unique list, there was enough information in your formulas for me to work around the duplication. Thank you also for your prompt response.

[Anonymous]

@KPI

No problem, I'm glad I could help. Don't forget to give me kudos 

Best of luck with Power BI in the future,

Martin