Calculate Previous Month Sales up to a specific date

[aleivar]

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.

[Jihwan_Kim · Accepted solution]

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