DAX formula to compare sales of the same week of this year with the previous year, accumulated

[Syndicate_Admin]

I hope you are doing well.

I am writing to you because I am trying to calculate a formula in DAX. The idea is to calculate the sales of the same week of this year and compare them with the previous year, accumulated. This involves adding the sales of the current week and the corresponding days of the same week of the previous year, always considering a seven-day period. For example, if the first and second days of week 19 of the year 2024 fall on May 6 and 7 (considering Monday as the first day of the week), and in the previous year (2023) they fell on May 1 and 2, I want to accumulate two days of sales, regardless of the exact dates, but only the number of days elapsed.

In addition, I am having another problem related to the filters. These are set based on the year 2024, as it is a comparison report of the current year with the previous year and the budget for the current year.

I managed to formulate it, but I am forcing the data, I would like it to be done automatically...

Accumulated AA Week = VARedDaysSelected = SELECTEDVALUE('Calendar'[#DíaSemana]) RETURN CALCULATE( [Sales], FILTER( ALL('Calendar'), 'Calendar'[#Año] = 2023 & 'Calendar'[#SemanaAño] = SELECTEDVALUE('Calendar'[#SemanaAño]) & 'Calendar'[#DíaSemana] IN {1, 2, 3} ) I appreciate your help...

[bhanu_gautam · Accepted solution]

@Syndicate_Admin , Try using below dax

Accumulated AA Week =
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYear])
VAR CurrentWeekday = SELECTEDVALUE('Calendar'[DayOfWeek])
VAR PreviousYear = CurrentYear - 1
VAR PreviousYearStartDate = DATE(PreviousYear, 1, 1)
VAR PreviousYearEndDate = DATE(PreviousYear, 12, 31)
VAR PreviousYearStartOfWeek = PreviousYearStartDate + (WEEKDAY(PreviousYearStartDate) - 1)
VAR PreviousYearEndOfWeek = PreviousYearEndDate - (7 - WEEKDAY(PreviousYearEndDate))

RETURN
CALCULATE(
[Sales],
FILTER(
ALL('Calendar'),
'Calendar'[Date] >= PreviousYearStartOfWeek && 'Calendar'[Date] <= PreviousYearEndOfWeek &&
'Calendar'[Year] = PreviousYear &&
'Calendar'[WeekOfYear] = CurrentWeek &&
'Calendar'[DayOfWeek] <= CurrentWeekday
)
)

[hackcrr · Accepted solution]

Hi, @Syndicate_Admin 

Below is an example of a DAX formula that may meet your needs:

Accumulated Sales This Year =   
VAR CurrentYear = SELECTEDVALUE('Calendar'[#Año])  
VAR CurrentWeek = SELECTEDVALUE('Calendar'[#SemanaAño])  
VAR TodayDate = MAX('Calendar'[Date]) 
VAR DaysInCurrentWeek =   
    CALCULATE(  
        COUNTROWS('Calendar'),  
        FILTER(  
            ALL('Calendar'),  
            'Calendar'[#Año] = CurrentYear &&  
            'Calendar'[#SemanaAño] = CurrentWeek &&  
            'Calendar'[Date] <= TodayDate  
        )  
    )  
VAR SalesThisYearToDate =   
    CALCULATE(  
        SUM([Sales]),  
        FILTER(  
            ALL('Calendar'),  
            'Calendar'[#Año] = CurrentYear &&  
            'Calendar'[#SemanaAño] = CurrentWeek &&  
            'Calendar'[Date] <= TodayDate  
        )  
    )  
VAR SalesPreviousYearToDate =   
    CALCULATE(  
        SUM([Sales]),  
        FILTER(  
            ALL('Calendar'),  
            'Calendar'[#Año] = CurrentYear - 1 &&  
            'Calendar'[#SemanaAño] = CurrentWeek &&  
            'Calendar'[Date] <= DATEADD(TodayDate, -YEAR(TodayDate) + CurrentYear - 1, DAY)  
        )  
    )  
RETURN  
    SalesThisYearToDate + SalesPreviousYearToDate

Only want the previous year's sales data, not the sum of two years' sales data. Therefore, we only need the SalesPreviousYearToDate part and may also need to tweak it to more accurately reflect your needs.
The final DAX formula might look similar:

Accumulated Sales Previous Year =   
VAR CurrentWeek = SELECTEDVALUE('Calendar'[#SemanaAño])  
VAR TodayDate = MAX('Calendar'[Date])  
RETURN  
    CALCULATE(  
        SUM([Sales]),  
        FILTER(  
            ALL('Calendar'),  
            'Calendar'[#Año] = YEAR(TodayDate) - 1 &&  
            'Calendar'[#SemanaAño] = CurrentWeek &&  
            'Calendar'[Date] <= DATEADD(TodayDate, -YEAR(TodayDate) + YEAR(TodayDate) - 1, DAY)  
        )  
    )

Best Regards,

hackcrr

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

[bhanu_gautam · Accepted solution]

@Syndicate_Admin , Try using below dax

Accumulated AA Week =
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYear])
VAR CurrentWeekday = SELECTEDVALUE('Calendar'[DayOfWeek])
VAR PreviousYear = CurrentYear - 1
VAR PreviousYearStartDate = DATE(PreviousYear, 1, 1)
VAR PreviousYearEndDate = DATE(PreviousYear, 12, 31)
VAR PreviousYearStartOfWeek = PreviousYearStartDate + (WEEKDAY(PreviousYearStartDate) - 1)
VAR PreviousYearEndOfWeek = PreviousYearEndDate - (7 - WEEKDAY(PreviousYearEndDate))

RETURN
CALCULATE(
[Sales],
FILTER(
ALL('Calendar'),
'Calendar'[Date] >= PreviousYearStartOfWeek && 'Calendar'[Date] <= PreviousYearEndOfWeek &&
'Calendar'[Year] = PreviousYear &&
'Calendar'[WeekOfYear] = CurrentWeek &&
'Calendar'[DayOfWeek] <= CurrentWeekday
)
)