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 have a below DAX where i need to calculate Total returns for all products for a selected client Since Inception(Start Date) to selected report date. i am using the below DAX. This works well but if i select recent dates it startes givinf memore exceed issue . Is there a way to optimize this DAX by further filters like (applying extra filter for selected client ) or any other approach for calculated table to be used? please help . Thanks
SinceInception =
VAR reportSelectedDate = MAX('Date Table'[LastDayOfMonth])
VAR yearCount=DATEDIFF([ProductStartDate],_selectedDate, DAY)/365
VAR _NewTotal =
CALCULATE(100 * (power( EXP ( ( SUM ( Client_Returns_Data[Returns] ) ) ),(1/yearCount))- 1),
CALCULATETABLE(
FILTER(VALUES('Date Table'[_Date]),
'Date Table'[_Date] >= [ProductStartDate] && 'Date Table'[_Date] <= _selectedDate
), ALL('Date Table')
))
Return
_NewTotal
@amitchandak , Thanks for reply.. i implemented the updated DAX u provided and seems to be working with initial testing. not yet tested completly.
Thanks again.. Can u please help understand .. how the updated DAX helped in improvement?
@ak77 , Try like
SinceInception =
VAR reportSelectedDate = MAX('Date Table'[LastDayOfMonth])
VAR yearCount=DATEDIFF([ProductStartDate],_selectedDate, DAY)/365
VAR _NewTotal =
CALCULATE(100 * (power( EXP ( ( SUM ( Client_Returns_Data[Returns] ) ) ),(1/yearCount))- 1),
FILTER(ALL('Date Table'),
'Date Table'[_Date] >= [ProductStartDate] && 'Date Table'[_Date] <= _selectedDate
)
)
Return
_NewTotal
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 |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |