Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Create a virtual table based on date and other slicer selection

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.
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

What are StartDate and EndDate?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    var startDate = STARTOFQUARTER('Calendar Date'[Date])
    var endDate = ENDOFMONTH('Calendar Date'[Date])
     
    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Anonymous 

      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
)