Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
I am trying to replicate this logic in Power BI. I have a column 'Fees' where I would like to sum the values in it when the condition 'Banked' is Yes (Y). But when the condition is met I want it to sum 'Fees' for the previous months each time the condition is met.
In the Excel above you will see Cell E4 = SUM(C2:C4) for Fund 1. So basically at each occurrence of Banked = "Y" then SUM the previous months values.
Is there a way to do this in either DAX or Power Query? I tried this but it obviously brings only the value back for that month where 'Banked' is Yes e.g. CALCULATE(SUM(Table1[FEES]), FILTER(Table1, Table1[BANKED] = "Y")) brings the FEES value for that Month only.
Appreciate anyone that can assist.
Regards
Solved! Go to Solution.
Here's a much simpler measure that works in all circumstances:
Measure =
var __tableToIterateOver =
filter(
'Table',
'Table'[BANKED] = "Y"
)
var __amount =
SUMX(
__tableToIterateOver,
var __currentDate = 'Table'[Date]
var __result =
calculate(
var __prevDateOfBankedStatus =
CALCULATE(
MAX( 'Table'[Date] ),
'Table'[BANKED] = "Y",
'Table'[Date] < __currentDate
)
var __output =
CALCULATE(
SUM( 'Table'[FEES] ),
__prevDateOfBankedStatus < 'Table'[Date],
'Table'[Date] <= __currentDate
)
return
__output,
// Leave only the filter on Fund.
ALLEXCEPT( 'Table', 'Table'[Fund] )
)
return
__result
)
return
__amount
Best
D
Hi,
Is it fair to say that everytime there is a Y in the banked column, we should sum the figures in the fees column of the 3 months ended the date of the current row
Hi @Ashish_Mathur Yes that's correct but it can be for any amount of months.
@v-kelly-msft I tried a variation of this before you posted also but get the following result:
As you can see I get the wrong total - the last value should be 3 595 699 and not 1 453 554.
Here's a much simpler measure that works in all circumstances:
Measure =
var __tableToIterateOver =
filter(
'Table',
'Table'[BANKED] = "Y"
)
var __amount =
SUMX(
__tableToIterateOver,
var __currentDate = 'Table'[Date]
var __result =
calculate(
var __prevDateOfBankedStatus =
CALCULATE(
MAX( 'Table'[Date] ),
'Table'[BANKED] = "Y",
'Table'[Date] < __currentDate
)
var __output =
CALCULATE(
SUM( 'Table'[FEES] ),
__prevDateOfBankedStatus < 'Table'[Date],
'Table'[Date] <= __currentDate
)
return
__output,
// Leave only the filter on Fund.
ALLEXCEPT( 'Table', 'Table'[Fund] )
)
return
__result
)
return
__amount
Best
D
Hi @Anonymous thank you very much for posting your measure, however don't get the correct figures coming through as you see below:
your measure brings the total which is great but it is not summing the fees as to what I get from the other measure posted by @v-kelly-msft . I am looking at it closer to figure it out as well
Ok this is my fault @Anonymous I missed an "=" sign here:
Looks like my previous post didn't go through @Anonymous -
I missed the "=" sign in your posted measure:
Your measure does indeed give me the correct figures:
Thank you for all your help! Much appreciated
Fareed
@Anonymous thanks I appreciate your input. I have been implementing similar concepts in writing my code.
@v-kelly-msft also appreciate your input, thanks again.
Cheers,
Fareed
Hi @Anonymous ,
1. Go to query editor > add column> Index column; (The column will be used for calculation)
2. Then create a measure as below:
Measure =
VAR a =
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[FUND] = SELECTEDVALUE ( 'Table'[FUND] )
)
VAR b =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[BANKED] = "Y"
&& 'Table'[FUND] = MAX ( 'Table'[FUND] )
)
)
VAR _mindate =
CALCULATE (
MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index] < MAX ( 'Table'[Index] )
&& 'Table'[BANKED] = "Y"
&& 'Table'[FUND] = SELECTEDVALUE ( 'Table'[FUND] )
)
)
VAR _multisum =
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[Date] > _mindate
&& 'Table'[FUND] = SELECTEDVALUE ( 'Table'[FUND] )
&& 'Table'[BANKED] = "N"
),
'Table'[FEES]
)
RETURN
IF (
MAX ( 'Table'[BANKED] ) = "Y",
IF (
b = 1,
SUMX ( a, 'Table'[FEES] ),
IF ( b > 1, _multisum + MAX ( 'Table'[FEES] ), BLANK () )
)
)
Finally you will see:
For the related .pbix file,pls click here.
@v-kelly-msft I had a go at this and it all works, thank you. But I don't get the Total showing, it only tallies up at the row context.
I did some digging and know there are issues sometimes with Measure Totals, and I found this link https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/td-p/547907/
It helped to an extent but I still don't get the correct Total, as you can see:
Any ideas? The total should be 21,733,910
Hi @Anonymous ,
Add a measure as below:
_total = SUMX(FILTER('Table','Table'[Measure]<>BLANK()),'Table'[Measure])
And you will see:
I have modified the .pbix file,pls click here.
@v-kelly-msft Wow, that's incredible. Thank you so much.
I would not have thought it would this complex to achieve in Power BI. I'm going to dissect this to fully understand what is happening.
One thing I noticed is that the Total for the Measure does not appear at the bottom. Is there a reason that happens?
Thank you again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |