Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Last 4 Weeks Total (minus previous week)

I need to achieve the total sales in the last 5 weeks minus the previous week. 

 

A measure for the total sales and a column for YearWeek in the "YYYYW00" format has been created. 

 

What I am trying to achieve is:


2022W01 =2021W51 Sales + 2021W52 Sales + 2022W01 Sales + 2022W02 Sales
2022W02 =2021W52 Sales + 2022W01 Sales + 2022W02 Sales + 2022W03 Sales
2022W03 =2022W01 Sales + 2022W02 Sales + 2022W03 Sales + 2022W04 Sales
2022W04 =2022W02 Sales + 2022W03 Sales + 2022W04 Sales + 2022W05 Sales
2022W05 =2022W03 Sales + 2022W04 Sales + 2022W05 Sales + 2022W06 Sales
2022W06 =2022W04 Sales + 2022W05 Sales + 2022W06 Sales + 2022W07 Sales
2022W07 =2022W05 Sales + 2022W06 Sales + 2022W07 Sales + 2022W08 Sales
2022W08 =2022W06 Sales + 2022W07 Sales + 2022W08 Sales + 2022W09 Sales
2022W09 =2022W07 Sales + 2022W08 Sales + 2022W09 Sales + 2022W10 Sales
2022W10 =2022W08 Sales + 2022W09 Sales + 2022W10 Sales + 2022W11 Sales
2022W11 =2022W09 Sales + 2022W10 Sales + 2022W11 Sales + 2022W12 Sales
2022W12 =2022W10 Sales + 2022W11 Sales + 2022W12 Sales + 2022W13 Sales
2022W13 =2022W11 Sales + 2022W12 Sales + 2022W13 Sales + 2022W14 Sales
2022W14 =2022W12 Sales + 2022W13 Sales + 2022W14 Sales + 2022W15 Sales
2022W15 =2022W13 Sales + 2022W14 Sales + 2022W15 Sales + 2022W16 Sales
2022W16 =2022W14 Sales + 2022W15 Sales + 2022W16 Sales + 2022W17 Sales
2022W17 =2022W15 Sales + 2022W16 Sales + 2022W17 Sales + 2022W18 Sales
2022W18 =2022W16 Sales + 2022W17 Sales + 2022W18 Sales + 2022W19 Sales
2022W19 =2022W17 Sales + 2022W18 Sales + 2022W19 Sales + 2022W20 Sales
2022W20 =2022W18 Sales + 2022W19 Sales + 2022W20 Sales + 2022W21 Sales

Any idea on how to achieve this? 

 

Thanks. 

Tutorial Requests
  • CNENFRNL's avatar
    CNENFRNL
    3 years ago

    Follow this pattern

    4WK = 
VAR __max =
    MINX(
        CALCULATETABLE(
            VALUES( DATES[_yyyywk] ),
            REMOVEFILTERS( DATES[YYYYWK] ),
            DATES[_yyyywk] > MAX( DATES[_yyyywk] )
        ),
        DATES[_yyyywk]
    )
VAR __min =
    MINX(
        TOPN(
            3,
            CALCULATETABLE(
                VALUES( DATES[_yyyywk] ),
                REMOVEFILTERS( DATES[YYYYWK] ),
                DATES[_yyyywk] <= MAX( DATES[_yyyywk] )
            ),
            DATES[_yyyywk]
        ),
        DATES[_yyyywk]
    )
RETURN
    CONCATENATEX(
        CALCULATETABLE(
            VALUES( DATES[YYYYWK] ),
            REMOVEFILTERS( DATES[YYYYWK] ),
            __min <= DATES[_yyyywk],
            DATES[_yyyywk] <= __max
        ),
        DATES[YYYYWK],
        ", "
    )

  • Anonymous's avatar
    Anonymous
    3 years ago

    Thanks. 

     

    I repurposed a part of your code to get exactly what I needed. 

     

    SUMX(
        TOPN(
            4,
            CALCULATETABLE(
                VALUES('Date'[YearWeek]),
                REMOVEFILTERS('Date'[YearWeek]),
                (INT('Date'[YearWeek])+1) < MAX('Date'[YearWeek])
            ),
            'Date'[YearWeek],DESC
        ),
        [Sales]
    )

4 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    3 years ago

    do you have a proper date and date table to work with?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Yes I have one. 

      Yes I have a one. 

      Please keep in mind that the week is Monday to Sunday. 

      Thanks. 

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Follow this pattern

    4WK = 
VAR __max =
    MINX(
        CALCULATETABLE(
            VALUES( DATES[_yyyywk] ),
            REMOVEFILTERS( DATES[YYYYWK] ),
            DATES[_yyyywk] > MAX( DATES[_yyyywk] )
        ),
        DATES[_yyyywk]
    )
VAR __min =
    MINX(
        TOPN(
            3,
            CALCULATETABLE(
                VALUES( DATES[_yyyywk] ),
                REMOVEFILTERS( DATES[YYYYWK] ),
                DATES[_yyyywk] <= MAX( DATES[_yyyywk] )
            ),
            DATES[_yyyywk]
        ),
        DATES[_yyyywk]
    )
RETURN
    CONCATENATEX(
        CALCULATETABLE(
            VALUES( DATES[YYYYWK] ),
            REMOVEFILTERS( DATES[YYYYWK] ),
            __min <= DATES[_yyyywk],
            DATES[_yyyywk] <= __max
        ),
        DATES[YYYYWK],
        ", "
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks. 

       

      I repurposed a part of your code to get exactly what I needed. 

       

      SUMX(
        TOPN(
            4,
            CALCULATETABLE(
                VALUES('Date'[YearWeek]),
                REMOVEFILTERS('Date'[YearWeek]),
                (INT('Date'[YearWeek])+1) < MAX('Date'[YearWeek])
            ),
            'Date'[YearWeek],DESC
        ),
        [Sales]
    )