Select Weekly Period and slice use's dates on report

[GrahamR99]

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

[v-huizhn-msft]

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]

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]

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]

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]

Hi @GrahamR99,

You can share the sample data by private message.

Best Regards,
Angelia

[GrahamR99]

Hello Angelia

How do I send you my Power BI file in a Private Message?

Regards

GrahamR99

[v-huizhn-msft]

Hi @GrahamR99,

You can store your .pbix file in onedrive, and post the share link in private message.



Best Regards,
Angelia

[GrahamR99]

Hello Angelia

How are you getting on with my file?

Regards

GrahamR99