Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to Solution.
@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]))
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
@AllisonKennedy @amitchandak Thank you very much. With the information you provided I was able to get the TB to populate a MTD amount.
@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]))
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
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.
@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.
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?
@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
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
User | Count |
---|---|
132 | |
74 | |
70 | |
58 | |
54 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |