Forum Discussion

Helper III

5 years ago

Solved

Dynamic previous week up to 4 weeks

Hello, I hope someone could please help me with this report im working on. I need to get the sum of each week for the last 4 weeks. It was still working well last year and i was using these formulas but since the begining of this year its not:

prevWEEK1 = CALCULATE([Billed_total],DateDimension[Week_Number] = WEEKNUM(TODAY(),1) -1)

prevWEEK2 = CALCULATE([Billed_total],DateDimension[Week_Number] = WEEKNUM(TODAY(),1) -2)

prevWEEK3 = CALCULATE([Billed_total],DateDimension[Week_Number] = WEEKNUM(TODAY(),1) -3)

prevWEEK4 = CALCULATE([Billed_total],DateDimension[Week_Number] = WEEKNUM(TODAY(),1) -4)

The totals right now are supposed to be WK1= 1195 =, WK2 = 26, WK3 = 2203 and WK4 = 2386

I have a Calendar Table

I'm using direct query

Thank you!

Need Help

v-lionel-msft

5 years ago

atjt217 ,

According to my understanding, the formula should be like this,

prevWEEK4 = 
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
    FILTER( ALL(DateDimension), [Year] = __year - 1),
    [Week_Number]
)
RETURN
SWITCH(
    __weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -3 ) ),
    __weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -2 ) ),
    __weekNum = 3, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
    __weekNum = 4, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
    CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum-4 ) )
)

prevWEEK3 = 
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
    FILTER( ALL(DateDimension), [Year] = __year - 1),
    [Week_Number]
)
RETURN
SWITCH(
    __weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -2 ) ),
    __weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
    __weekNum = 3, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum  ) ),
    CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -3 ) )
)

prevWEEK2 = 
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
    FILTER( ALL(DateDimension), [Year] = __year - 1),
    [Week_Number]
)
RETURN
SWITCH(
    __weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
    __weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum  ) ),
    CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -2 ) )
)

prevWEEK1 = 
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
    FILTER( ALL(DateDimension), [Year] = __year - 1),
    [Week_Number]
)
RETURN
SWITCH(
    __weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum) ),
    CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -1 ) )
)

Best regards,
Lionel Chen

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

8 Replies

amitchandak
Super User
5 years ago
atjt217 , refer my blog , have additional column on week rank in you date table and use those. If needed; you can have date table in power bi even in direct query mode

See if this can help

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

Date table in power bi in Direct query https://www.youtube.com/watch?v=cQfJ0GmQ5os&list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA&index=7
- atjt217
  Helper III
  5 years ago
  I tried the formula on your blog as a calculated measure:
  Week Rank2 = RANKX(ALL(DateDimension[Date]),(DateDimension[Week Start date],,ASC,Dense))
  But im getting an error that the syntax ASC is incorrect.
  Could you please tell me if i happen to miss anything?
  
  Thanks for you help amitchandak!

v-lionel-msft

Community Support

5 years ago

atjt217 ,

According to my understanding, the formula should be like this,

prevWEEK4 = 
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
    FILTER( ALL(DateDimension), [Year] = __year - 1),
    [Week_Number]
)
RETURN
SWITCH(
    __weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -3 ) ),
    __weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -2 ) ),
    __weekNum = 3, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
    __weekNum = 4, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
    CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum-4 ) )
)

prevWEEK3 = 
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
    FILTER( ALL(DateDimension), [Year] = __year - 1),
    [Week_Number]
)
RETURN
SWITCH(
    __weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -2 ) ),
    __weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
    __weekNum = 3, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum  ) ),
    CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -3 ) )
)

prevWEEK2 = 
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
    FILTER( ALL(DateDimension), [Year] = __year - 1),
    [Week_Number]
)
RETURN
SWITCH(
    __weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
    __weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum  ) ),
    CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -2 ) )
)

prevWEEK1 = 
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
    FILTER( ALL(DateDimension), [Year] = __year - 1),
    [Week_Number]
)
RETURN
SWITCH(
    __weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum) ),
    CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -1 ) )
)

Best regards,
Lionel Chen

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

atjt217
Helper III
5 years ago
It works. Thank you so much for your help!

v-lionel-msft
Community Support
5 years ago
Hi atjt217 ,

When WEEKNUM(TODAY(), 1) is less than or equal to 4, the previous one week, two weeks, three weeks, and four weeks should be the last few weeks of the previous year.

Such as current week number is 1:

prevWEEK1 = VAR __weekNum = WEEKNUM(TODAY(), 2) VAR __year = YEAR(TODAY()) VAR __maxWeekNum = MAXX( FILTER( ALL(Sheet5), [Year] = __year - 1), [WeekNum] ) RETURN IF( __weekNum = 1, CALCULATE( SUM(Sheet5[Value]), FILTER( Sheet5, [Year] = __year - 1 && [WeekNum] = __maxWeekNum ) ), CALCULATE( SUM(Sheet5[Value]), FILTER( Sheet5, [WeekNum] = __weekNum - 1 ) ) )

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- atjt217
  Helper III
  5 years ago
  Hi Lionel,
  
  I tried to use the formula:
  prevWEEK1 =
  VAR __weekNum = WEEKNUM(TODAY(), 2)
  VAR __year = YEAR(TODAY())
  VAR __maxWeekNum =
  MAXX(
  FILTER( ALL(DateDimension), [Year] = __year - 1),
  [Week_Number]
  RETURN
  IF(
  __weekNum = 1,
  CALCULATE(SUMX(vu_Bi_UnableToFill_2019ToCurrent,[Billed_total]),
  FILTER(
  DateDimension,
  [Year] = __year - 1 && [Week_Number] = __maxWeekNum
  )
  ),
  CALCULATE(SUMX(vu_Bi_UnableToFill_2019ToCurrent,[Billed_total]),
  FILTER(
  DateDimension,
  [Week_Number]= __weekNum - 1
  )
  )
  )
  But im getting an error. Did i happen to skip on something. Could you please advise? Thank you
v-lionel-msft
Community Support
5 years ago
Hi atjt217 ,

Please try this formula again.

prevWEEK1 = VAR __weekNum = WEEKNUM(TODAY(), 1) VAR __year = YEAR(TODAY()) VAR __maxWeekNum = MAXX( FILTER( ALL(DateDimension), [Year] = __year - 1), [Week_Number] ) RETURN IF( __weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ), CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number]= __weekNum - 1 ) ) )

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- atjt217
  Helper III
  5 years ago
  Hello, Thank you for the reponse. It was working for Weeks 1-3 but week 4 is returning blank.
  
  For week 4 here is what i used:
  prevWEEK4 =
  VAR __weekNum = WEEKNUM(TODAY(), 1)
  VAR __year = YEAR(TODAY())
  VAR __maxWeekNum =
  MAXX(
  FILTER( ALL(DateDimension), [Year] = __year - 1),
  [Week_Number]
  )
  RETURN
  IF(
  __weekNum = 1,
  CALCULATE(
  [Billed_total],
  FILTER(
  DateDimension,
  [Year] = __year - 1 && [Week_Number] = __maxWeekNum
  )
  ),
  CALCULATE(
  [Billed_total],
  FILTER(
  DateDimension,
  [Week_Number]= __weekNum - 4
  )
  )
  )
  
  Is there something else i should have changed?

Forum Discussion

Dynamic previous week up to 4 weeks

8 Replies

Related content

Calculate previous week total and previous 2 weeks total

Previous Week DAX

Calculate previous week values

Previous Week Sales

Count of previous week