Create a virtual table based on date and other slicer selection

[PallaviR]

I need to create a virtual table which will be a subset of fact table based on slicer selection and calendar date selection. Please help.

[v-yangliu-msft]

Hi  @PallaviR ,

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

[PallaviR]

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.

[tamerj1]

@PallaviR 

Just creat a measure 

Amount = SUM ( Tansactions[Sales] )

insert a matrix visual, placr Customers in the rows and the Amount measure in the values

[PallaviR]

No this will not help. Dynamically select the date from slicer and get the subset of transaction table.

[tamerj1]

@PallaviR 
this is exactly what Powe Bi does. Why do you think you won't get the required result? Am I missing something?

[tamerj1]

Hi @PallaviR 

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

[PallaviR]

Hi @tamerj1 

Can you please tell me how to add here date filters. It is not working. 

SUMMARIZECOLUMNS(Transaction[Customer],FILTER('Calendar Date','Calendar Date'[Date] >= startDate && 'Calendar Date'[Date] <= endDate),"Sales", calculate(SUM(Transaction[SALES])))

Thanks,

Pallavi

[tamerj1]

What are StartDate and EndDate?

[PallaviR]

var startDate = STARTOFQUARTER('Calendar Date'[Date])

var endDate = ENDOFMONTH('Calendar Date'[Date])

[tamerj1]

@PallaviR 

Sorry just noticed your reply. You may try this 

Table1 =
var startDate = 
    STARTOFQUARTER('Calendar Date'[Date] )
var endDate = 
    ENDOFMONTH('Calendar Date'[Date])
RETURN
FILTER (
    SUMMARIZE (
        Transaction,
        Transaction[Customer],
        'Calendar Date'[Date],
        "Sales", SUM ( Transaction[SALES] )
    ),
    'Calendar Date'[Date] >= startDate
        && 'Calendar Date'[Date] <= endDate
)