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 ...
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

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

      Hello Angelia

      I don't want to upload my .pbix as it contain sensitive data. Is there another way we can get to what I need?

       

      Regards

       

      GrahamR99

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

        Hi GrahamR99,

        You can share the sample data by private message.

        Best Regards,
        Angelia