Filtering table before grouping using date slicer

[87seven]

In powerbi I have 2 tables in my model.First table has unique orders ids and 2nd table has order transactions.

Both tables have a date column.I would like to have first table with unique orders as main table then have a derived table from the 2nd table that aggregates by order id and sums the transaction amount by order.
Each table will be filtered by date and the date filter will be a common date slicer.
I will then join the 1st table to the derived aggragated table.

The challenge I am having is that I am new to powerbi and not sure how to achieve the filtering on 2nd table before I aggregate. i.e we only want to find sum of transactions for transactions within a specific date range.

So filter first using a date slicer then aggragate thereafter.
It seems once powerbi creates an aggragted table during modeling, there is no way to affected this from the visualization slicer.

How can I achieve this requirement.
Thank you !
 

[87seven]

Hi @v-tianyich-msft 

Please see the intended result 

There are 2 source tables on the left Orders Main table and Orders Transaction table
There can only be 1 order in the Orders Main table
There can be multiple order transactions in the Orders Transaction table related to an order.

We need a report that can filter by date range, in this case date from  2022-10-15 to 2024-02-15.
This is the table shown on the right Orders Main Table augmented with transactios amount.
Having a single date filterfrom  2022-10-15 to 2024-02-15 that affeacts both tables on the left, only 3 orders satisfy this in Orders Main table , order 1 and 2 and 3and Only 6 transactions satisfy this in Orders Transaction table, 2,3,4,5,6,7

A join table with only these values is what we want to show in our report whereby there is order 1, 2 and 3 and the aggregated transactions by order whereby even though order 1 has 3 transactions, transaction 1 falls out of the date range so the aggregated sum of order 1 transctions excludes trasnaction 1.
The date range includes all of order 2s transactions, so they are all summed 
The date range excludes transaction 8 and 9 that belong to order 3

Best regards

[v-tianyich-msft]

Hi @87seven ,

Is it possible to present the sample data as well as the expected results in a tabular format?

Best regards,
Community Support Team_ Scott Chang

[87seven]

Hi @v-tianyich-msft 

I may have misunderstood.
May you give a demo of what you mean given my previous explanation that we have 2 tables A and B and A should be the main one bringing in B's aggreagated data.
Are you perhaps saying we can bring in A's data into the measure we create in B ?

Thanks

[87seven]

Hi @v-tianyich-msft 

Consider a case of say a shop.
There is a master table with unique orders, say table A.
So its 1 entry per order in this table.

There is also another table B, where transactions per order can be captured.
In the transcation table, multiple payments can be paid against that order, each payment done on a specific date.

In table A, its a master record of the order so there could be up to 10 columns of information about an order in table A.
Table A will be the main data table where we show all of its information in our report.
We need to bring in the aggreagated sum of amount from table B to enrich main table A.

Before we aggregate the data in table A, we need to filter out based on a date range.
Therefater, the aggregated sum of each order should then be joined to table A.

This combined data of all columns of A and the aggregated sum of orders in B then forms our report.
So its a left join of A and B 
A is also date filtered, since the records in it have a date range, B is also date filtered, so that we get transactions for an order in A only from a specific time period.

Thanks

[v-tianyich-msft]

Hi @87seven ,

It seems that all that is needed is to put the date from the table into the summary table and cancel the sum of cost. filter This measure represents the total. If I have misunderstood you, please provide more information.

Best regards,
Community Support Team_ Scott Chang

[87seven]

Hi @v-tianyich-msft  , thank you so much for responding to the post !

I have managed to replicate the example you gave.
So I created the separate date table and a measure in the base table. 
Am I also able to include the count as well in the CALCULATE(***) so that I know that my SUM was derived from so many number of transactions ?

The solution so far wont work for me as the end goal is to take the aggregated values and join them to a base table.
The main thing is that while this shows how we can filter a table based on a date then aggregate, we really want this to be either available in the modeling phase so that I can join a main table to the summary.
Is this possible to achieve this at modelling phase or in visualization phase ?

If its not, How can I achieve this so that the aggregated values from a seperate transaction table can be joined to a seperate main table based on oderID 

There are actually 2 detail tables that need to be summarized , and joined to the main table, each bringing a specific amount to do with the orderid.They both have dates as well.
Would the solution that works for joining 1 aggregated table to the main table be extnsible to work for both, in terms of filtering then aggregation 

Thank you !

[v-tianyich-msft]

Hi @87seven ,

First, create an unrelated schedule, then use this as a slicer. then create this MEASURE for filtering:

Table 2 = SUMMARIZE('Table',[Date])

Filter = var _min = MIN('Table 2'[Date])
var _max = MAX('Table 2'[Date])
RETURN CALCULATE(SUM('Table'[Cost]),FILTER(ALLEXCEPT('Table','Table'[OrderID]),[Date]>=_min&&[Date]<=_max))

Best regards,
Community Support Team_ Scott Chang