cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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:

 date amt1 amt2 amt3 calc jan 10 5 1 14 feb 20 6 11 15 mar 30 7 21 16

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:

 date calc calc YTD jan 14 14 feb 15 34 mar 16 64

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 2
Helper II

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.

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 )

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.