Dynamic previous week up to 4 weeks

[atjt217]

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! 

[v-lionel-msft · Accepted solution]

@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]

It works. Thank you so much for your help! 

[v-lionel-msft]

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]

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?

[v-lionel-msft]

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]

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 

[amitchandak]

@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-La...
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]

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!