cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mpatel
Helper I
Helper I

PREVIOUSMONTH with wrong total and subtotal

Hello,

I am trying to calculate the previous month value of transactions with the following code :

Transactions PM = 
VAR LastDayAvailable =
    CALCULATE (
        MAX ( TKT[TKT_DATE] ),
        ALL ( TKT )
    )
VAR CurrentDates =
    FILTER (
        VALUES ( Date[Date] ),
        Date[Date] <= LastDayAvailable
    )
VAR Result =
    CALCULATE (
        [Number of transactions],
        PREVIOUSMONTH ( CurrentDates )
    )
RETURN Result

 

The results are good concerning each indivudual months. However the totals are wrong as seen on screenshot.

 

Capture d’écran 2020-08-25 à 20.31.42.png

 

Can you please help me on that ?

 

Mohammad

 

1 ACCEPTED SOLUTION

@mpatel - 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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@mpatel , if you have date you can use time intelligence with date table

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

 

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Dear @amitchandak

 

Thanks for the reply but your solution does not work for me.

 

As I said, my problem is with the totals and subtotals.

 

Regards,

 

Mohammad

@mpatel , because the previous month  is in context so grand total will on last month

try like

sumx(summarize(Table, Table[year], Table[Month],"_1",[Transactions PM]),[_1])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@mpatel - 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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

Dear @Greg_Deckler,

 

Thank you very much, you put me on the right track.

 

Just add the following code in the return :

 

 

RETURN IF(HASONEVALUE(Date[Date_id_mois]);Result)

 


It works great !

 

Mohammad

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.