YTD for calculated values not working

[superjam]

Hello PowerBI experts!

I have 3 tables:

1. Balances

Investment balances for each month, like so:

Date	Final Balance
31-12-22	100.000
31-01-23	101.000
28-02-23	102.100
31-03-23	114.000
30-04-23	96.800
31-05-23	96.500
30-06-23	100.300
31-07-23	144.000

2. Movements

Investment movements, like dividends, new investments, withdraws, etc., per date:

Type	Date	Amount
Income	31-01-23	1.500
Income	31-03-23	800
Income	31-03-23	20.000
Withdraw	31-03-23	-10.000
Income	30-04-23	1.400
Withdraw	30-04-23	-18.000
Income	31-05-23	1.300
Income	31-07-23	1.600
Income	31-07-23	40.000

Note that there may be more than one movement per month, or there may be none.

3. Calendar

The usual one. With active relationaships through the "Date" field of both tables above.

Now the measures I added:

1. 

Final Balance = COALESCE(
SUM( Balances[Final Balance] )
, 0 )

2.

Initial Balance = COALESCE(
CALCULATE( Final Balance, PREVIOUSMONTH( Calendar[Date] )
)
, 0 )

3.

Income =
SUMX( FILTER( 'Movements',
'Movements'[Type] = "Income"
),
'Movements'[Amount]
)

4.

Withdraw =

SUMX( FILTER( 'Movements',
'Movements'[Type] = "Withdraw"
),
'Movements'[Amount]
)

5.

Revenue Month =

Final Balance - Inicial Balance - Income + Withdraw

6.

Lastly, I tried to calculate de YTD revenue with various methods, but could not come up with a correct result.

The table visual works fine, except for the YTD measure, that should look like this:

Month	Inicial Balance	Income	Withdraw	Final Balance	Revenue Month	Revenue YTD
Jan	100.000	1.500		101.000	-500	-500
Feb	101.500			102.100	600	100
Mar	101.500	20.800	-10.000	114.000	1.700	1.800
Apr	112.300	1.400	-18.000	96.800	1.100	2.900
May	95.700	1.300		96.500	-500	2.400
Jun	97.000			100.300	3.300	5.700
Jul	97.000	41.600		144.000	5.400	11.100

Instead, that column on the table seems to sum up only the final balances, instead of the revenue month.

The three methods I tried are:

1.

TOTALYTD( Revenue Month, Calendar[Date] )

2.

var _SelectedYear = SELECTEDVALUE( Calendar[Year] )
var _FirstDay = DATE( _SelectedYear, 1, 1 )
var _LastDay = MAX( Calendar[Date] )
RETURN

CALCULATE( Revenue Month,
FILTER( ALLSELECTED( Calendar ),
Calendar[Date] >= _FirstDay &&
Calendar[Date] <= _LastDay
)
)

3.

SUMX(
FILTER(
ALLSELECTED( Calendar ),
Calendar[Date] <= MAX ( Calendar[Date] )
),
Revenue Month
)

All three attempts obtain different values, but no one is correct.

Any hints?

Thank you and regards.

[Ashish_Mathur]

Hi,

I cannot understand your expected table at all.  Based on the first 2 tables that you have shared, show the expected result clearly.

[mickey64]

Is '800' correct?

Type Date Amount
Income 31-01-23 1.500
Income 31-03-23 800
Income 31-03-23 20.000
Withdraw 31-03-23 -10.000
Income 30-04-23 1.400
Withdraw 30-04-23 -18.000
Income 31-05-23 1.300
Income 31-07-23 1.600
Income 31-07-23 40.000

[superjam]

Yes, @mickey64 , it is correct. It is only sample input data.

Thank you and regards.

[Greg_Deckler]

@superjam It's hard to tell if this is right or not because I don't think the sample data provided matches up with the result you posted. But try this. PBIX is attached below signature. I had to change your Initial Balance formula because yours didn't work for me.

YTD = 
    VAR __MaxDate = MAX('Calendar'[Date])
    VAR __Table = 
        SUMMARIZE(
            FILTER(
                ALL('Movements'),
                [Date] <= __MaxDate
            ),
            [Date],
            "__YTD", [Revenue Month]
        )
    VAR __Result = SUMX( __Table, [__YTD])
RETURN
    __Result

[superjam]

Thank you so much, @Greg_Deckler .

I copied your YTD formula and only adapted it to match table and field names in my database. Unfortunaltely it did not work. I must have something different in my data logic!

In your pbix I see everything like explained.

¿How can I upload a pbix file?

Thanks and regards

[superjam]

Hello, @Greg_Deckler , @mickey64 , @Ashish_Mathur 

Here is an extract of my project:

Example.pbix

Thank you again and regards,

[Greg_Deckler · Accepted solution]

@superjam Here you go:

Measure = 
    VAR __MesAnoNUm = MAX('Calendar'[MesAñoNum])
    VAR __Table = FILTER ( SUMMARIZE( ALLSELECTED('Calendar'), [MesAño], [MesAñoNum], "__Value", [Revenue month] ), [MesAñoNum] <= __MesAnoNum )
    VAR __Result = SUMX( __Table, [__Value] )
RETURN
    __Result

[superjam]

Hello @Greg_Deckler 

You rock!! It worked fine. THANK YOU very much. 

Now it is my turn to discover and learn how you did it (and why otherwise it did not work).

And I think I will by your book. Really.

Kind regards.

[Greg_Deckler]

@superjam Thanks. It's interesting because at it's heart, it's a running total. And you have a Calendar table so, in theory, you should have been able to construct that running total using CALULATE and/or using time intelligence functions so not entirely certain where things were going haywire other than it's likely some complexity buried in your measures that eventually become part of the running total. A lot of running totals are simple SUM's but yours is much more complex so that would be my guess as far as what was going on.