Forum Discussion

superjam's avatar
superjam
Helper II
2 years ago

Problems with a YTD calculation

Hello.

 

I have three tables:

1. Movimientos, among others, with the following columns:

- date

- amt1

- amt2

- amt3

There may be multiple lines for the same date, with different amt values.

 

2. Calendar, the usual one, with the "date" column

 

3. Measures, with the following column (very simple!), among others, and working fine:

calc = Movimientos[amt1] + Movimientos [amt2] - Movimientos[amt3]

 

The tables "Movimientos" and "Calendar" have an active relationship, connecting the columns "date" and "date".

 

I made a simple table visual, with months in the rows and numbers and calc in values, like so:

 

dateamt1amt2amt3calc
jan105114
feb2061115
mar3072116

 

Until here, everything works fine.

 

What I need is the YTD of "calc", so I did a measure like the following:

calc YTD = TOTALYTD( calc, calendar[date] ).

 

Strangely, I obtained something like this:

 

datecalccalc YTD
jan1414
feb1534
mar1664

 

So, the first month is OK. For the next month, it adds the first argument (amt1) of the "calc" formula, instead of adding the result of that formula. The same for the following months. Does this make sense?

 

Why is this happening? 

 

Thank you and regards.

 

 

2 Replies

  • CoreyP's avatar
    CoreyP
    Solution Sage

    I feel like this could be a SUMX thing. So, instead of calc = amt1 + amt2 - amt3, try calc = SUMX( Movimientos, amt1 + amt2 - amt3 )

  • Hi CoreyP 

     

    Thank you for your help. The SUMX thing actually worked, ...in part.

    The YTD calculation is now correct. This part is corrected!!

     

    But, now a little problem appeared in the calculation for the "calc" measure: if some values of amt1, amt2, or amt3 is blank, then the end result is also blank.

     

    I suppose this happens, because for some months, there is no register for some amt values. I mean, for example, there is no line of february with amt2.

     

    Is there a way to correct this?

     

    Thank you again and regards.