Create a virtual table based on date and other slicer selection

Hi Anonymous 

ready to support you. Please explain the issue in details and provide sample data and screenshots 

Thank you tamerj1 . Let me explain the scenario here. I have this transaction table here.

I have a slicer to select the date range. I have slicers to selection product, brand etc. Based on date range frpm slicer and prod + brand selection as option, I have to create a virtual table which will be a subset of the transaction table. It will take only those records that fall under selected date range from slicer additionaly if there is any slicer selection done on prod/brand.

Example - If I select only date range slicer for customers between 1-Aug-2022 to 20-Sep-2022. And no selection of prod, brand. Then my output should show aggregated sales for the customer within that date range

In this case, this should be my output

Scenario 2 - Suppose 

If I select only date range slicer for customers between 1-Aug-2022 to 20-Sep-2022. And Brand 2 is selected. Then my output should show aggregated sales for the customer within that date range and only customer sales with brand 2. In this case, this should be my output. Please help.

Hi  Anonymous ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
CALENDAR(MIN('Table'[Transcation Date]),MAX('Table'[Transcation Date]))

2. Create measure.

Sales-Measure =
var _selectmin=MINX(ALLSELECTED('Table 2'),'Table 2'[Date])
var _seletmax=MaxX(ALLSELECTED('Table 2'),'Table 2'[Date])
var _selectpro=SELECTEDVALUE('Table'[Product])
var _selectbrand=SELECTEDVALUE('Table'[Brand])
return
SWITCH(
    TRUE(),
NOT( ISFILTERED('Table'[Product]))&&NOT( ISFILTERED('Table'[Brand])),CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Transcation Date]>=_selectmin&&'Table'[Transcation Date]<=_seletmax&&'Table'[Customer]=MAX('Table'[Customer]))),
NOT( ISFILTERED('Table'[Product])),CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Transcation Date]>=_selectmin&&'Table'[Transcation Date]<=_seletmax&&'Table'[Brand]=_selectbrand&&'Table'[Customer]=MAX('Table'[Customer]))),
NOT( ISFILTERED('Table'[Brand])),CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Transcation Date]>=_selectmin&&'Table'[Transcation Date]<=_seletmax&&'Table'[Product]=_selectpro&&'Table'[Customer]=MAX('Table'[Customer]))),
CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Transcation Date]>=_selectmin&&'Table'[Transcation Date]<=_seletmax&&'Table'[Product]=_selectpro&&'Table'[Brand]=_selectbrand&&'Table'[Customer]=MAX('Table'[Customer]))))

3. Result:

Scenario 1：

Scenario 2：

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