Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Can't filter by week

Hi!

I'm new to Power BI

 

I've facing some trouble trying to filter sales by week, as well as check WoW growth handling week offset.
I've made a calendar table as shown below (it's in portuguese, "date" is setted up as date, "semana" means week and "ano" means year, both setted up as numbers). Them I've related the date with my fact sales table and so on, but I just can't use the field "week" as an axis or filter to anything.

 

I'm pretty sure I'm missing something. Any thoughts?

Ty in advance 

 

 

 

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

    Hi,

     

    For your issue, please check your relationships:

    Try this measure to show growth of week:

    Growth of Week = 
var LastWeekSales = CALCULATE(SUM('Fact'[Sales]),FILTER(ALLSELECTED('Calendar'),'Calendar'[WeekNo]=MAX('Calendar'[WeekNo])-1))
return
IF(LastWeekSales<>BLANK()&&MAX('Fact'[Sales])<>BLANK(),(SUM('Fact'[Sales])-LastWeekSales)/LastWeekSales)

    And it works well here:

    Here is my test pbix file, please check it:

    pbix 

    If you still have issue, please share some sample fact data, tables' relationships and expected result as a screenshot for me to help you further.

    Expect your reply!

     

    Best Regards,

    Giotto Zhi

2 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi,

     

    For your issue, please check your relationships:

    Try this measure to show growth of week:

    Growth of Week = 
var LastWeekSales = CALCULATE(SUM('Fact'[Sales]),FILTER(ALLSELECTED('Calendar'),'Calendar'[WeekNo]=MAX('Calendar'[WeekNo])-1))
return
IF(LastWeekSales<>BLANK()&&MAX('Fact'[Sales])<>BLANK(),(SUM('Fact'[Sales])-LastWeekSales)/LastWeekSales)

    And it works well here:

    Here is my test pbix file, please check it:

    pbix 

    If you still have issue, please share some sample fact data, tables' relationships and expected result as a screenshot for me to help you further.

    Expect your reply!

     

    Best Regards,

    Giotto Zhi