Forum Discussion

PTSAMP's avatar
PTSAMP
Regular Visitor
3 years ago

Week over Week Change filter by product

I want to do the report of Week over Week Change as table below.

 

And my formula for Week Num is WeekNum = WEEKNUM(Weekly[Production date])

Demand Week N = CALCULATE(SUM(Weekly[Tons]),FILTER(ALL(Weekly),Weekly[WeekRank]=MAX(Weekly[WeekRank])))

Demand Week N-1 = CALCULATE(SUM(Weekly[Tons]),FILTER(ALL(Weekly),Weekly[WeekRank]=MAX(Weekly[WeekRank])-1))

but when I use this formula the qty didn't change when i filter by product it shows the total demand for that week instead.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    PTSAMP , use a separate week/date table, that will make it easy , in that case all will only remove week filter on the complete data filter

     

    example

    CALCULATE(SUM(Weekly[Tons]),FILTER(ALL(WeekDim),WeekDim[WeekRank]=MAX(WeekDim[WeekRank])-1))

     

    WeekDim is joined to your table on yearweek or date

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s