Forum Discussion

htittoto's avatar
htittoto
Frequent Visitor
6 years ago
Solved

Filter by week And Last Week

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

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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.

5 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    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's avatar
      htittoto
      Frequent Visitor
      6 years ago

      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,

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    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.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      2 years ago

      Hello, with power query, how can we calculate the current week with a custom column and then with a current week filter show us "Last week" with a conditional column