Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
annamalaig
Frequent Visitor

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

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)

Transaction table.JPGdate.JPG

 

summary.JPGdetail.JPG

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.

 

 

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @annamalaig ,

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

relationship.PNG

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @annamalaig ,

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

relationship.PNG

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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. 

relationship.PNG

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

 


 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.