Quick DAX help for last month and the preceding month values

[Dan_Alton]

Hi - I've got a DAX measure to give me the latest availabe data from a sales table

Sales Last Month Sum = VAR endDate = LASTDATE('Sales[Date])

RETURN

CALCULATE(

    SUM('Sales'[Value]),

    'Sales'[Date] = endDate

)

How do I get a DAX sum to show the value for the month before? I'd then be looking to show the difference in the latest month from the month that preceeded it - is there a way of doing it without having a Calendar table set up? Any help greatly appreciated!

[FreemanZ]

hi @Dan_Alton ,

Not sure if i fully get you, supposing your sales table look like:

date	value
9/1/2023	1
10/1/2023	1
10/2/2023	1
11/1/2023	1
11/2/2023	1
11/3/2023	1
12/1/2023	1
12/2/2023	1
1/1/2024	1
1/2/2024	1
1/3/2024	1
1/4/2024	1

try

1) add calculated table like:

dates = 
ADDCOLUMNS(
    CALENDAR(MIN(sales[date]), MAX(sales[date])),
    "yymm", FORMAT([Date], "yy/mm")
)

2) relate sales[date] with dates[date]

3) plot a table visual with dates[yymm] with a measure like:

TotalPM = 
CALCULATE(
    SUM(sales[value]),
    DATEADD(dates[Date],-1, MONTH)
)

it worked like:

[v-yaningy-msft]

Could you please provide the data table without sensitive information and how you expect the output to look like?

[Dan_Alton]

hi - my table is very simple - thanks

Apr-23	37.9
May-23	39.2
Jun-23	41.0
Jul-23	39.6
Aug-23	39.5
Sep-23	39.1
Oct-23	39.9