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.
I have created an aging formula for an education institution. the aging is calculating correctly. though the total figures are incorrect
there are three tables in use, the main Date table (sf), a calender table (calender) and mapping table for main data table (mapping).
Aging 0-60 =
var agingto=0
var agingfrom=60
var totatoutstanding=sum(SF[Line Amt])
var maxdate = LASTDATE(Calender[Date])
var positivevalues = CALCULATE(sum(SF[Line Amt]), FILTER(sf, SF[Itm Eff Dt_6]>DATEADD(maxdate, -agingfrom, DAY) && SF[Itm Eff Dt_6]<=DATEADD(maxdate, -agingto, DAY)), Mapping[Transaction ]="Fee - Adjustable" || Mapping[Transaction ]="tax" || Mapping[Transaction ]="Fee", SF[Line Amt]>0)
var previousaging=0
var result = MIN(SUM(SF[Line Amt])-previousaging, positivevalues)
return
if(HASONEFILTER(SF[ID]),
if (result=0, BLANK(), result), sumX(sf, result))
Solved! Go to Solution.
Please try this expression. Replace Table with your actual table name and use this measure in place of your current one (which it references).
NewMeasure = SUMX(VALUES(Table[ID]), [Aging 0-60])
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks. Your solution worked.
though can you explain why it needed to reference it again and can I resolve this using one measure
Please try this expression. Replace Table with your actual table name and use this measure in place of your current one (which it references).
NewMeasure = SUMX(VALUES(Table[ID]), [Aging 0-60])
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |