topic Need help in dax caulation for this week and last months same week sales in DAX Commands and Tips

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

Anonymous — Wed, 10 Aug 2022 12:58:27 GMT

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

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

daXtreme — Wed, 10 Aug 2022 13:08:35 GMT

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

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

Jihwan_Kim — Wed, 10 Aug 2022 13:36:52 GMT

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 ) ) )

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

Anonymous — Thu, 18 Aug 2022 00:53:11 GMT

Hi Anonymous ,

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