Forum Discussion

atjt217's avatar
atjt217
Icon for Helper III rankHelper 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 ...
Need Help
  • v-lionel-msft's avatar
    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.

     

v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity 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's avatar
atjt217
Icon for Helper III rankHelper 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