Forum Discussion

astano05's avatar
astano05
Helper III
5 years ago

Filter table based on measure

I have a table of open orders with entry date, quantity, order number, etc. This is connected to a customer table and an item table. 

 

I have a single select slicer for entry date. This filters the report for the Top 15 Items with measures for the Qty Sold on the Selected Date, and the sum of the Qty Ordered before the selected date. I have another table on the report grouped by customer with the same measures.

 

HEre are the measures:

Qty Ordered Selected Date =
Var SelectedDate = SELECTEDVALUE('Open Sales Orders Line'[ORDER_ENTRY_DATE])
Return
CALCULATE(sum('Open Sales Orders Line'[Quantity Ordered]),'Open Sales Orders Line'[ORDER_ENTRY_DATE]=SelectedDate)
 
Qty Ordered Before Selected Date =
var MinDate = CALCULATE(min('Open Sales Orders Line'[ORDER_ENTRY_DATE]),filter(all('Open Sales Orders Line'))
var SelectedDate = SELECTEDVALUE('Open Sales Orders Line'[ORDER_ENTRY_DATE])
Return
CALCULATE(sum('Open Sales Orders Line'[Quantity Ordered]),DATESBETWEEN('Open Sales Orders Line'[ORDER_ENTRY_DATE],MinDate,SelectedDate))-[Qty Ordered Selected Date]
 
All of this works properly. But now I want another table at the bottom that has the individual order information (entry date, order number, item, quantity) - however with the slicer for entry date, this filters the table for just that one date but I need everything from the selected date to all dates prior. If I remove the interaction of the slicer, I will get orders after the selected date. 
 
Is there any way to accomplish this?
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  astano05 ,

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.