Forum Discussion

GrahamR99's avatar
GrahamR99
Icon for Resolver I rankResolver I
9 years ago

Select Weekly Period and slice use's dates on report

Hello

I have a table that has week numbers and the dates for these weeks, it look like the image.

 

What I want to do is present the user the Week_Number in a slicer and then the report of my sales data will only display data between the Start_Date and the End_Date.

 

Is this possible what are the steps to do it, or can I do it another way?

 

Regards

 

GrahamR99

 

 

 

8 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi GrahamR99,

    I don't know the sales data you have, please share it for further analysis if the following isn't what your expected. I assume you have two tables, one is like the one shown screenshot, another one is sale table including sale_amount and sale_date. 

    First, you need to create two measure to get the start date and end date based on slicer.

    Start_date=MINX(ALLSELECTED(DateTable),DateTable[RW_START_DATE])
End_date=MAXX(ALLSELECTED(DateTable),DateTable[RW_END_DATE])


    Second, create a measure using the formula.

    sales value =
CALCULATE (
    MAX ( 'Sales Table'[Sales] ),
    FILTER (
        'Sales Table',
        AND (
            'Sales Table'[Sales_Date] >= [Start_date],
            'Sales Table'[Sales_Date] <= [End_date]
        )
    )
)


    Finally, create a table visual, select the 'Sales Table'[Sales_Date], 'sales value' as value level, you will get expected result.

    Best Regards,
    Angelia

     

    • GrahamR99's avatar
      GrahamR99
      Icon for Resolver I rankResolver I
      9 years ago

      Hello Angelia

      I have followed your steps, but it has not applied a filter to my data, it just gives a date for the week I have selected.

       

      What I want is it to remove all data that is not in my selection, from the slicer.

       

       

      I'm not ready to try this on my sales data, so I tried to apply it to my stock data insted and it to filter the order_due date.  Order_Due_N is my measure.  I was expecting to only see the top line but as you can see there are other line in my report.

       

      Can you tell me what I am doing wrong?

       

      Regards

       

      GrahamR99

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi GrahamR99,

        Got it, it seems like your scenario is different from mine. Could you please share your .pbix file for further analysis?

        Best Regards,
        Angelia