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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Shorty_AO
Helper I
Helper I

Trial Balance YTD to MTD

Hello,

 

I am new to Power BI desktop and am having difficulties on the best way to get a MTD amount from my Trial Balance.  Trial Balance shows amounts in YTD.  I have created a Calender table and created a relationship to the Trial Balance.  When I enter the following Measure in the TB table it does not populate any amounts.  Ultimately, I will need to calculate a two month average on specific accounts base off the BUCode &  Plant ID to then calculate a MOH calculation.  Any help would be much appreciated.

Capture.PNG

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@Shorty_AO If you're using a MEASURE (which is correct for this) you will need to put the Measure in a visualization in the report view to see its value. Your screenshots are of the Data view, so you won't see the result of your measure there (only COLUMNS show up in Data view).

 

To get Month to Date, follow a similar pattern to what you have done, but use DATESMTD instead of PREVIOUSMONTH:
MTDTotal = CALCULATE( SUM(TB_Data_Table[Source Amount]), DATESMTD(Calendar[Date]))

 

Then you can use this to get Prev MTD:

Prev MTD Total = CALCULATE( [MTDTotal], DATESMTD(Calendar[Date]))

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

7 REPLIES 7
Shorty_AO
Helper I
Helper I

@AllisonKennedy @amitchandak Thank you very much.  With the information you provided I was able to get the TB to populate a MTD amount.

AllisonKennedy
Super User
Super User

@Shorty_AO If you're using a MEASURE (which is correct for this) you will need to put the Measure in a visualization in the report view to see its value. Your screenshots are of the Data view, so you won't see the result of your measure there (only COLUMNS show up in Data view).

 

To get Month to Date, follow a similar pattern to what you have done, but use DATESMTD instead of PREVIOUSMONTH:
MTDTotal = CALCULATE( SUM(TB_Data_Table[Source Amount]), DATESMTD(Calendar[Date]))

 

Then you can use this to get Prev MTD:

Prev MTD Total = CALCULATE( [MTDTotal], DATESMTD(Calendar[Date]))

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

lbendlin
Super User
Super User

MTD requires day level transaction granularity - does your data support that? 

 

Your formula calculates the entire value of the previus month. That is unrelated to MTD.

The calender table is created as follows.  Capture.PNG

@Shorty_AO , As your data is YTD, You need to do this month minus last month, if you have a date then create a date calendar and then try

 


MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

or

last Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

 

Then

Current month = [MTD Sales] -[last MTD Sales]

Current month = [MTD Sales] -[last Month Sales]

 

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.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

I tried the soultions noted above.  However, I still don't get amounts to populate.  Could my calender table be preventing the measure to work? 

Capture.PNG

@Shorty_AO  Yes, have you marked the calendar table as a Date Table in Power BI? You must do that for Time Intelligence functions to work: 

https://allisonkennedycv.blogspot.com/2020/04/dimdate-what-why-and-how.html


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.