Forum Discussion

superjam's avatar
superjam
Helper II
2 years ago
Solved

YTD for calculated values not working

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:

TypeDateAmount
Income31-01-231.500
Income31-03-23800
Income31-03-2320.000
Withdraw31-03-23-10.000
Income30-04-231.400
Withdraw30-04-23-18.000
Income31-05-231.300
Income31-07-231.600
Income31-07-2340.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:

 

MonthInicial BalanceIncomeWithdrawFinal BalanceRevenue MonthRevenue YTD
Jan          100.000    1.500         101.000-500-500
Feb          101.500          102.100600100
Mar          101.500  20.800-10.000        114.0001.7001.800
Apr          112.300    1.400-18.000          96.8001.1002.900
May            95.700    1.300           96.500-5002.400
Jun            97.000          100.3003.3005.700
Jul            97.000  41.600         144.0005.40011.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.

 

 

 

 

 

  • 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

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    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's avatar
      superjam
      Helper II

      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

  • 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's avatar
      superjam
      Helper II

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

       

      Thank you and regards.

       

  • Hi,

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