Forum Discussion
Aggregation of iterative date-based function
I FINALLY figured it out after an entire day of research/learning. Thank you bhanu_gautam for starting me down the right path.
The aggregation issue was solved by defining several variables WITHIN an iterator function, which in this case was a sumx. Here is the final code that worked for me (please note I also had a separate calendar table 'Date' which was connected to the fact table):
FinallyWorkingMeasure =
VAR md =
DATE(year(MAXX(ALLSELECTED('Date'[Date]),'Date'[Date])),month(MAXX(ALLSELECTED('Date'[Date]),'Date'[Date])),1) -- this was just formatting the selected date to match my data which showed first day of each monthRETURNsumx('Table',VAR actualcost = 'Table'[Actual Cost]VAR latestmargin =calculate(MAX('Table'[Budget Expected Total Profit Margin %]),ALLEXCEPT('Table','Table'[Unique ID]),'Date'[Date]=md)RETURNdivide(actualcost, 1 - latestmargin, 0))
I FINALLY figured it out after an entire day of research/learning. Thank you bhanu_gautam for starting me down the right path.
The aggregation issue was solved by defining several variables WITHIN an iterator function, which in this case was a sumx. Here is the final code that worked for me (please note I also had a separate calendar table 'Date' which was connected to the fact table):
FinallyWorkingMeasure =
VAR md =