Filter by week And Last Week

[htittoto]

Hello,

I'm trying to create a graph that shows a recent tendency. I must be able to filter it by week and year. But also, i would like to see the last week result so i may observe a tendency.

In resume, i have a graph and two filters (year and week). I must show on graph 3 numbers: (acumulated result of selected year - wich is ok; result of selected week - wich is also ok; and result of the previous selected week - wich is my doubt). I would like to do that in a way a could easily filter weeks and years using a filter outside the graph/formula.

Could any one help me?

Thanks in advance

[v-zhenbw-msft · Accepted solution]

Hi @htittoto ,

The dark blue curve’s formula like this,

last_week = 
var last_w = MAX('Table'[week])-1
return
CALCULATE(SUM('Table'[values]),FILTER(ALLSELECTED('Table'),'Table'[week]=last_w))

If you have any questions, please kindly ask here and we will try to resolve it.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[htittoto]

@v-zhenbw-msft @Thanks for the answer, it has worked perfectly! 

[v-zhenbw-msft]

Hi @htittoto ,

We can use the following steps to meet your requirement.

1. Create year column and week column.

year = YEAR('Table'[Date])

week = WEEKNUM('Table'[Date])

2. Then we can create a measure. And add two slicers, one is week, another is year. The result like this,

If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your sample files to OneDrive For Business and share the link here and don't contain any Confidential Information or Real data in your reply.     

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[htittoto]

@v-zhenbw-msft, first thanks for replying.

I've done everything the same way you did. The only thing i couldn't do was the last week curve. How did you build the formula for the dark blue curve (in the images you've sent)? That is the only thing i couldnt do. What is the formula?

Thanks,