Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
Hi,
I cannot understand your expected table at all. Based on the first 2 tables that you have shared, show the expected result clearly.
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 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
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
Hello, @Greg_Deckler , @mickey64 , @Ashish_Mathur
Here is an extract of my project:
Thank you again 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
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.
@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.