Calculate Previous Month Sales up to a specific date

aleivar — Fri, 21 Apr 2023 01:44:41 GMT

Hi,

I have the following functions:

Sales MTD = CALCULATE( [Total Sales], DATESMTD( Calendar_Lookup[Date] ) )

Sales MTD Previous = CALCULATE( [Sales MTD], DATEADD( Calendar_Lookup[Date], -1, MONTH ) )

I want to calculate the Sales MTD Previous but up to a specific date (max date).

My model looks like this:

Any help is appreciated.

Re: Calculate Previous Month Sales up to a specific date

Jihwan_Kim — Fri, 21 Apr 2023 03:32:54 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 datamodel.

Sales MTD prevmonth upto specific date: = VAR _selectedyear = YEAR ( MAX ( 'Calendar'[Date] ) ) VAR _selectedmonth = MONTH ( MAX ( 'Calendar'[Date] ) ) VAR _prevmonthdate = CALCULATE ( MAX ( 'Calendar'[Date] ), DATEADD ( 'Calendar'[Date], -1, MONTH ) ) RETURN IF ( NOT ISBLANK ( _prevmonthdate ), IF ( _selectedmonth <> 1, CALCULATE ( [Total sales:], FILTER ( ALL ( 'Calendar' ), YEAR ( [Date] ) = _selectedyear && MONTH ( [Date] ) = _selectedmonth - 1 && 'Calendar'[Date] <= _prevmonthdate ) ), CALCULATE ( [Total sales:], FILTER ( ALL ( 'Calendar' ), YEAR ( [Date] ) = _selectedyear - 1 && MONTH ( [Date] ) = 12 && 'Calendar'[Date] <= _prevmonthdate ) ) ) )

topic Calculate Previous Month Sales up to a specific date in DAX Commands and Tips

Calculate Previous Month Sales up to a specific date

Re: Calculate Previous Month Sales up to a specific date