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 ,

 

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's avatar
    atjt217
    Icon for Helper III rankHelper 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?