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.
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
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 |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |