Drill through report to filter the table based on the date range

[annamalaig]

I'm trying to build summary and detail report.

Summary to calculate measure based on the date range, can't define relationship between the date table & transaction table given that each measure uses different date fields for calculations.

Consider this data model (sample version to solve real scenario)

TotalRevenue = CALCULATE(SUM(JobRevenue[Revenue]),FILTER(JobRevenue,JobRevenue[StartDate]>=Min('Date'[Date]) && JobRevenue[StartDate]<=MAX('Date'[Date])))

I'm calculating total revenue for a date range and drill through added to the Job code.

On the detail report, i couldn't apply the date filter. I should see only data for Jan 2019.

Please help. Attached the Power BI file as well.

[v-piga-msft · Accepted solution]

Hi @annamalaig ,

Based on your data sample and formula, I have made a test. 

More details, please refer to my attached pbix.

If you still need help, please share your desired output so that we could give further advice.

Best  Regards,

Cherry

[annamalaig]

Thanks Cherry - Appreciate you taking time to explain with the file.

I was doing the drill through based on the Job Code instead of the date and disabled the relationship between the date table and transaction table assuming that this condition is based on the date range. Both are incorrect and agree with your solution.

---

@v-piga-msft wrote:

Hi @annamalaig ,

Based on your data sample and formula, I have made a test. 

More details, please refer to my attached pbix.

If you still need help, please share your desired output so that we could give further advice.

Best  Regards,

Cherry

---

[annamalaig]

Cherry,

Can you help me with this use case or scenario?

Let's say that I have an another transaction table (or data table) called Expenses which needs to be filtered based on both Job code and date filter. Currently the date table filters Jobs table (in the data model referred as "Table") with the join between 'Table' Start Date and 'Date' Date. Also there is a join between Jobs table ('Job Code') and Expenses table ('Job code') already and Power BI not allowing an another join with the date - between Expenses and Date with the same date range applied to Jobs/Expenses.

Can you please help me with options to filter two transaction tables - Jobs/Expenses and applying the same date between both tables and keeping the job code as a join between Jobs/Expenses. Not sure if I have to create date aliases and handle two different dates.