cancel
Showing results for
Did you mean:
Post Patron

## Previous Month to Date

Hi I have a issue with Matrix table sub total,

I have craeted below measures

Amount Sales = SUM(Sales[Amount])
Prev Month To Date = IF([MTH]=1, 0, CALCULATE ([Amount Sales], DATEADD(Calender[Date],-1, MONTH)))
Sales Amount Mth = [Amount Sales] - [Prev Month To Date]

When I added the Sales Smount Mth to Matrix table its giving the each month value correctly. But total SUM for each Year is wrong.

Can someone help.

Thank you.
1 ACCEPTED SOLUTION
Super User

@cham Try:

``````Measure Total =
VAR __Total = SUMX(SUMMARIZE('Table',[End of Month],"__PrevMonth",[PreviousMonthMeasure]),[__PrevMonth])
RETURN
IF(HASONEVALUE('Table'[End of Month]),[PreviousMonthMeasure],__Total)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
12 REPLIES 12
Super User

@cham You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

``````Amount Sales = SUM(Sales[Amount])

Prev Month To Date =
VAR __Date = MAX('Calendar'[Date])
VAR __PreviousMonth = DATE(YEAR(__Date),MONTH(__Date)-1,DAY(__Date))
VAR __FirstOfPrevMonth = DATE(YEAR(__PreviousMonth),MONTH(__PreviousMont),1)
RETURN
IF([MTH]=1, 0, CALCULATE ([Amount Sales], Calender[Date]>=__FirstOfPrevMonth, Calendar[Date]<=__PreviousMonth))

Sales Amount Mth = [Amount Sales] - [Prev Month To Date]``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Post Patron

The amount which are showing for each month is corrcet. The issue I am having is total for each year.

Ex: 2019 Total value is not the SUM of the 2019 Months Value.

Thanks

Super User

@cham Oh, I misread. This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Post Patron

2020 year total is giving e the corrcet sum of 2020 months

But the measures I used to get the previous month value is correct. Every month is giving the previous month value but the Total of all the months values showing in sub total is incorrect.

WhatI can do for that.

thanks.

Super User

@cham See MM3TR&R it's essentially the same issue as with measure totals: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

Hi,

I am confused but I'd also like to help you.  Please show clearly in an MS Excel file, the result you are expecting.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Post Patron

This is the Power BI table - which is not givingthe current SUB total of 2019 months

This is the excel data set, Power BI sub total needs to be as below highlited total.

Thanks,

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@cham Try:

``````Measure Total =
VAR __Total = SUMX(SUMMARIZE('Table',[End of Month],"__PrevMonth",[PreviousMonthMeasure]),[__PrevMonth])
RETURN
IF(HASONEVALUE('Table'[End of Month]),[PreviousMonthMeasure],__Total)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Post Patron

Its not giving the correct totals what I want. Thank measure change my month totals as well.

Super User

@cham Can you post sample data as text so that we can replicate the issue?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Post Patron

Hi Grey Your measure worked on this. Thank you so much for your help.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors