Forum Discussion

SmithyBowMassiv's avatar
SmithyBowMassiv
Regular Visitor
8 years ago

FIlter Data Based On Slicer Selection

I have  data table with sales products by day for the currrent quarter [tblSales]

InvoiceDate | Product | Qty Sold

I have a table that has date period selections [tblDateSelections]

Date Period | Start Date | End Date

Within this table i have entries for Yesterday, WTD, Last Complete Week etc all with the relevant start date,end date and integer identifying the selection made

EG: Last Complete Week | 19-02-2018 | 26-02-2018 | 1

 

Within the report i have a slicer based on date selection table, i can see this working and the value change when i make a selection.

msrDatePeriod = IF(HASONEVALUE(DatePeriod[TimePeriod]),VALUES(DatePeriod[Value]),5)

 

What I want to do is create a column in tblSales that flags the relevant records based on the date selection, ideally flagging with a 1. Which i would then use as a filter criteria for visualisations. This way i can show QTD and any other date period selected.

 

Any help would be greatly appreciated.

Cheers

4 Replies

  • dramus's avatar
    dramus
    Icon for Continued Contributor rankContinued Contributor
    8 years ago

    Do you have a limited number of values in the "Date Period" column?

     

    If so then you can create a new column on your [tblSales] table for each value.

     

    E.g. This Week = if(lookupvalue(tblDateSelections[Start Date],tblDateSelections[Date Period],"This Week") <= [InvoiceDate] && lookupvalue(tblDateSelections[End Date],tblDateSelections[Date Period],"This Week") >= [InvoiceDate],1,0)

     

    Assuming you had a date range called "This Week".

    • SmithyBowMassiv's avatar
      SmithyBowMassiv
      Regular Visitor
      8 years ago

      This solution would only give me a 1 or 0 for that date period. I have columns within the tblSales that flag each date range already eg WTD-Flag, MTD-Flag etc. In an ideal world what I want to do is;

      • select a date range period from a slicer which is populated from a table with all the date range selections
      • this selection to then change a value in a column called MasterDate-Flag within tblSales which would update all relevant records within this column to a 1 or 0 dependant on the date period selected
      • this column would then be used as filter criteria for the report, page or visualisation

      Hope this makes sense and thanks for the proposed answer.

      Cheers

      • dramus's avatar
        dramus
        Icon for Continued Contributor rankContinued Contributor
        8 years ago

        Let me see if I can restate the problem, to see if I understand.

         

        You want a column on the Sales data that shows whether this record has been selected based on the filtered values in the Dates table?