Forum Discussion
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:
| 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.
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_DecklerCommunity 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- superjamHelper 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
- superjamHelper II
Hello, Greg_Deckler , mickey64 , Ashish_Mathur
Here is an extract of my project:
Thank you again and regards,
- mickey64Super User
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- superjamHelper II
- Ashish_MathurSuper User
Hi,
I cannot understand your expected table at all. Based on the first 2 tables that you have shared, show the expected result clearly.