topic Calculate Previous Month Total When Two Dates Are Equal in DAX Commands and Tips

Calculate Previous Month Total When Two Dates Are Equal

InfiniteSheldon — Tue, 18 Jul 2023 22:39:09 GMT

I currently have 3 tables - sales, customer, date. For the date table, I am using the [Month End] column (always the last day of the month) on the 'Date Table' to filter the Sales table (see the screenshot below).

I have written a measure below to calcualte the total mobile sales when the transaction date equals the posting date:

Current Month Mobile Sales =
Calculate(
Sum(Sales[Total Amount],
Customer[Customer_Type] = "Mobile",
Sales[Transaction Date] = Sales[Posting Date]

)

How can I get the "previous month Mobile Sales"? I wrote the DAX below:

Calculate(
[Current Month Mobile Sales],

DATEADD('Date Table'[Month End], -1, MONTH)
)

However, it only works when there are 31 days in a month. How should I fix it to get the correct "previous month Mobile Sales" when filtering by the [Month End] column?

Thanks!

Re: Calculate Previous Month Total When Two Dates Are Equal

some_bih — Wed, 19 Jul 2023 08:53:44 GMT

Hi InfiniteSheldon did you try DAX function TOTALMTD? Hope this help

https://learn.microsoft.com/en-us/dax/totalmtd-function-dax?WT.mc_id=DP-MVP-4025372

Re: Calculate Previous Month Total When Two Dates Are Equal

InfiniteSheldon — Wed, 19 Jul 2023 16:03:04 GMT

Hi some_bih, could you please be more specific? I am not sure how TOTALMTD is going to help calculate previous month total when two dates are the same.

Re: Calculate Previous Month Total When Two Dates Are Equal

some_bih — Wed, 19 Jul 2023 17:55:49 GMT

Hi InfiniteSheldon

Apply as measure X=TOTALMTD(

[Current Month Mobile Sales], 'Your Date table date column key')

Check result. Hope this help

Re: Calculate Previous Month Total When Two Dates Are Equal

InfiniteSheldon — Wed, 19 Jul 2023 19:06:23 GMT

Hi some_bih, I don't think that's the solution. However, I figured it out myself using

PREVIOUSMONTH rather than DATEADD('Date Table'[Month End], -1, MONTH). Thanks anyway for your help!