PowerPivot - relating data by date range

1
votes

Does anyone know what DAX function I should use to display information from one table in another table.

I've got 2 tables in my data model:

Tasks - Task ID - Task Name - Start Date - End Date

Fiscal Periods - ID - Period Name - Start Date - End Date

What I'm trying to do is for each Task, add a calculated column the is populated with the corresponding Fiscal Period ID. I'm trying to add a filter or calculation that specifies: - if the task start date is between Fiscal Start Date and Fiscal End Date, return the fiscal period id.

Anyone have any ideas? Thanks, Ro

1
excelexcel-formulapowerpivotpowerquery
This link has a more flexible way of implementing what you are looking for... powerpivotpro.com/2011/11/the-ultimate-date-table - guitarthrower
could you upload some sample data? - Petr Havlik

1 Answers

0
votes

This might help:

  1. In case Fiscal Period ID is a number:

=CALCULATE(MAX(Periods[ID]),FILTER(Periods,Periods[Start Date]<=Tasks[Start Date] && Periods[End Date]>=Tasks[Start Date]))

  1. In case Fiscal Period ID is not a number:

Put Start Date to calculated column first, say 'Period Start Date'

=CALCULATE(MAX(Periods[Start Date]),FILTER(Periods,Periods[Start Date]<=Tasks[Start Date] && Periods[End Date]>=Tasks[Start Date]))

and then use LOOKUPVALUE for ID

=LOOKUPVALUE(Periods[ID],Periods[Start Date],Tasks[Period Start Date])