Need help in dax caulation for this week and last months same week sales

[kruthikav]

Date	Sales
10-Aug-2022	100
11-July-2022	60
08-July-2022	40
09-July-2022	22
09-Aug-2022	234

I have 2 column Date and Sales. I need to calculate below:

If today date in Aug 10 I need dax for:
1. This week sales-last month's same week sales
i.e sales from 8,9,10 minus sales from July 8,9,10

2. Similary today's sale minus last month's same day sale
i.e sale of Aug 10-sale of July 10

[v-yangliu-msft]

Hi  @kruthikav ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Year = YEAR('Table'[Date])

Month = MONTH('Table'[Date])

Week = WEEKNUM('Table'[Date],1)

weeknumber =
RANKX(FILTER(ALL('Table'),
'Table'[Year]=EARLIER('Table'[Year])&&'Table'[Month]=EARLIER('Table'[Month])),[Week],,ASC,Dense)

2. Create measure.

Dax1 =
var _today=TODAY()
var _week=
MAXX(FILTER(ALL('Table'),'Table'[Date]=_today),[weeknumber])
var _weeksales=
SUMX(FILTER(ALL('Table'),'Table'[Month]=MONTH(_today)&&'Table'[weeknumber]=_week),[Amount])
var _lastmonth=
SUMX(FILTER(ALL('Table'),'Table'[Month]=MONTH(_today)-1&&'Table'[weeknumber]=_week),[Amount])
return
_weeksales - _lastmonth

Dax2 =
var _today=TODAY()
var _todaysalse=
SUMX(FILTER(ALL('Table'),'Table'[Date]=_today),[Amount])
var _lastmonthsameday=
SUMX(FILTER(ALL('Table'),'Table'[Date]=DATE(YEAR(_today),MONTH(_today)-1,DAY(_today))),[Amount])
return
_todaysalse - _lastmonthsameday

3. Result:

Best Regards,

Liu Yang

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

[Jihwan_Kim]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your dataset.

WTD sales vs previousmonth same dates sales: = 
VAR _wtdtable =
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[Start of Week] = MAX ( 'Calendar'[Start of Week] )
    )
VAR _daynumberlist =
    SUMMARIZE ( _wtdtable, 'Calendar'[Day] )
VAR _previousmonthwtdtablestartdate =
    DATE ( IF (
        MAX ( 'Calendar'[Month] ) = 1,
        MAX ( 'Calendar'[Year] ) - 1,
        MAX ( 'Calendar'[Year] )
    ), IF ( MAX ( 'Calendar'[Month] ) = 1, 12, MAX ( 'Calendar'[Month] ) - 1 ), DAY ( MINX ( _wtdtable, 'Calendar'[Date] ) ) )
VAR _previousmonthwtdtablefinishdate =
    DATE ( IF (
        MAX ( 'Calendar'[Month] ) = 1,
        MAX ( 'Calendar'[Year] ) - 1,
        MAX ( 'Calendar'[Year] )
    ), IF ( MAX ( 'Calendar'[Month] ) = 1, 12, MAX ( 'Calendar'[Month] ) - 1 ), DAY ( MAXX ( _wtdtable, 'Calendar'[Date] ) ) )
VAR _currentmonthwtd =
    CALCULATE ( [Sales measure:], _wtdtable )
VAR _previousmonthexpected =
    CALCULATE (
        [Sales measure:],
        DATESBETWEEN (
            'Calendar'[Date],
            _previousmonthwtdtablestartdate,
            _previousmonthwtdtablefinishdate
        )
    )
RETURN
    IF (
        NOT ISBLANK ( CALCULATE ( [Sales measure:], PREVIOUSMONTH ( 'Calendar'[Date] ) ) ),
        _currentmonthwtd - _previousmonthexpected
    )

sales vs last month same day sales: =
IF (
    NOT ISBLANK (
        CALCULATE ( [Sales measure:], DATEADD ( 'Calendar'[Date], -1, MONTH ) )
    ),
    [Sales measure:]
        - CALCULATE ( [Sales measure:], DATEADD ( 'Calendar'[Date], -1, MONTH ) )
)

[daXtreme]

Hi there

You probably thought it'd be easy as 1-2-3.... No, it's not.

Please read this article: Week-Based Time Intelligence in DAX - SQLBI