Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
The below formula seems to be working fine, But is very slow , can anyone suggest ways to improve.
https://drive.google.com/file/d/1C4cKXalkDq95yFt5BWdVwwoxL0oAiJ8U/view?usp=sharing
Solved! Go to Solution.
Here is a different way to write it that gets the same result. Please check if its is more performant with your actual data.
New Measure =
VAR summary =
ADDCOLUMNS (
SUMMARIZE (
'Date',
'Date'[Year],
'Date'[MonthName],
"maxdate", MAX ( 'Date'[Date] )
),
"@result",
VAR maxdate = [maxdate]
RETURN
CALCULATE (
SUM ( 'To Collect'[To Collect] ),
ALLSELECTED ( 'Date'[Date] ),
'Date'[Date] <= maxdate
)
- CALCULATE (
SUM ( 'Unapplied Receipts'[AdvPayment] )
)
)
RETURN
SUMX (
FILTER (
summary,
[@result] > 0
),
[@result]
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try and check if it is faster...
Cumulative Net To Collect =
VAR CurrentDate = MAX ( Date[date] )
VAR CumulCollect = CALCULATE (
SUM ( Table[Net Cumulative To Collect] ),
FILTER ( ALL ( Table), Date[date] <= CurrentDate )
)
RETURN
CumulCollect
This formula is giving incorrect results
When a measure refer to other measure, sometime unexpected result comes. Maybe trying it again with actual formula instead of [Net Cumulative To Collect] might work. I recomend to test the fomular with simple table first,
I have attached the sample file in the link, could you please suggest , how to make it work
Here is a different way to write it that gets the same result. Please check if its is more performant with your actual data.
New Measure =
VAR summary =
ADDCOLUMNS (
SUMMARIZE (
'Date',
'Date'[Year],
'Date'[MonthName],
"maxdate", MAX ( 'Date'[Date] )
),
"@result",
VAR maxdate = [maxdate]
RETURN
CALCULATE (
SUM ( 'To Collect'[To Collect] ),
ALLSELECTED ( 'Date'[Date] ),
'Date'[Date] <= maxdate
)
- CALCULATE (
SUM ( 'Unapplied Receipts'[AdvPayment] )
)
)
RETURN
SUMX (
FILTER (
summary,
[@result] > 0
),
[@result]
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Could you please kindly explain why this formula is performing well
Hi @Antmkjr
Are you sure your version of the measure is not performing well in terms of speed? I tried a few different options and measured them with the performance analyzer in Power BI and yours seems to be the fastest by quite a bit.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |