Forum Discussion
Cummulative YTD Totals By Month Error
Good Morning All,
I have the attached table that I'm getting incorrectly calculated totals. I'm looking to get YTD totals by month, I am using the same measures just on different accounts I want to get the totals for. My issue is when I try to use the category of Month from the date Hierarchy for my LOC table it no longer calculates the YTD totals by month. But when I use the actual date for the field instead of the Hiearchy Month it does calculate correctly. I am using the same measures for by AR Balance table as well and it is calculating correctly for both the Hiearchy Month field and the actual date field. I've tried to use a seperate date table for the LOC as well but that still didn't work for me. I am wondering if it's because the LOC table does not have transactions in every month. Any help would be appreciated.
AR Balance SUM Measure
AR Total Amount Measure:
FBergamaschi Thank you for the reply again, I tried that solution you gave and it did not work. I think it is odd that my formula works for my AR Table but not the LOC Table. The only thing I can think of is that the LOC Table doesn't have transactions in every month so it is messing up the calculations. Every date for the transaction will be the 1st of the month for example 12/1/2025 12:00 AM, so when I use the actual date field for my visual instead of the hierarchy provided month label everything works correctly
9 Replies
- FBergamaschi
Super User
Hi jr2314 , to help you , I neeed the following, but a question first: why do not you use DATESYTD?
Apart from the above, to help you:
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
FBergamaschi Thank you, here is a small sample of the data in the table being used, I have one for AR and one for LOC. They have the same rows and columns. I have not tried DATESYTD yet and I can see if that works.
GLCo Mth GLTrans GLAcct Jrnl GLRef SourceCo Source ActDate DatePosted Description BatchId Amount RevStatus Adjust
99 8/1/2025 1980 11000 AR 206 2 JB 8/13/2025 8/14/2025 Invoice/Client A/Project 001 206 58,892.37 0 N 99 8/1/2025 1981 11000 AR 206 2 JB 8/13/2025 8/14/2025 Invoice/Client B/Project 002 206 86,097.70 0 N 99 8/1/2025 1982 11000 AR 206 2 JB 8/13/2025 8/14/2025 Invoice/Client C/Project 003 206 102,822.13 0 N - FBergamaschi
Hi jr2314,
if yo uwrite your own DAX code things are more difficult, that's why I was asking. Microsoft has created functions to simplify time calculations.
Anyway, with your own DAX I see that you need to removefilters from the date table (this step is performed automatically by DATESYTD, but not automatically if you do not use it), so you should try the following (I took one measure as an example)
AR Balance SUM =
VAR CurrentDate =MAX('AR GL'[Mth])
RETURN
CALCULATE([AR Total Amount SUM], FILTER(ALL('AR GL'),'AR GL'[Mth] <= CurrentDate,
REMOVEFILTERS ( YourDateTableName )
))If that does not solve, please share the pbix or send it via private message and I shall look into itIf this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your threadWant to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
DATESYTD is not a solution that I can tell, I am trying to get the total balance for each month. So for example January was 100 and February was 200 so in the February Row I would expect the total to be 300 not 200 and that is what's happening. It is working when I use the actual date time field as a category in the table but when I use the Hierarchy Month field (the name Jan, Feb etc..) it no longer does that calculation.
