Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
InfiniteSheldon
Frequent Visitor

Calculate Previous Month Total When Two Dates Are Equal

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

 

InfiniteSheldon_2-1689719072134.png


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! 

 

 

1 ACCEPTED SOLUTION

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! 

View solution in original post

4 REPLIES 4
some_bih
Super User
Super User

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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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. 

Hi @InfiniteSheldon 

Apply as measure X=TOTALMTD(

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

Check result. Hope this help

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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! 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.