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
cham
Post Patron
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

@cham Try:

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

12 REPLIES 12
Greg_Deckler
Super User
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...

 

In your case, maybe:

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]

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler 

 

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.

 

Can you please help for this issue?

 

Thanks

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler 

 

Please see the screesnhot below,

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

cham_0-1632367141875.png

 

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.

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

 

Please find the below details,

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

cham_0-1632370651708.png

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

cham_1-1632370681356.png

 

Thanks,

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@cham Try:

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

hi @Greg_Deckler ,

 

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

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi Grey Your measure worked on this. Thank you so much 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.

Top Solution Authors