Cummulative YTD Totals By Month Error

[jr2314]

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 Balance SUM =

VAR CurrentDate  =

    MAX('AR GL'[Mth])

RETURN

CALCULATE(

    [AR Total Amount SUM], FILTER(ALL('AR GL'),'AR GL'[Mth] <= CurrentDate

        )

)

AR Total Amount Measure:

AR Total Amount SUM = SUM('AR GL'[Amount])

LOC Balance SUM =

VAR CurrentDate =

    MAX ( 'LOC GL'[Mth] )

RETURN

CALCULATE (

    [LOC Total Amount SUM],

    FILTER (

        ALL ( 'LOC GL'[Mth] ),

        'LOC GL'[Mth] <= CurrentDate

    )

)

LOC Total Amount SUM =

SUM('LOC GL'[Amount])

[FBergamaschi]

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

[jr2314]

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.

[jr2314]

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

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want 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

[jr2314 · Accepted solution]

@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

[v-aatheeque]

Hi @jr2314 

Thanks for the update. Just to confirm, is the issue now resolved when using the actual Date column instead of the Date hierarchy?

If the problem still occurs when using the Month hierarchy, could you please let us know what part is still not working as expected (for example, incorrect totals, missing months, or wrong percentages) ?  This will help us understand the exact behavior and suggest a more appropriate solution.

[v-aatheeque]

Hi @jr2314 
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further

[v-aatheeque]

Hi @jr2314 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

[v-aatheeque]

Hi @jr2314 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.