Need some help with some measures and logic in DAX

[Carlos_Noob]

Hello everyone,

I would appreciate your help with some metrics or the method to achieve the following objective:

I would like to build a Power BI report that, starting from a table tAG, with the following fields:

• Date | Store | Agent | Expenses | Revenues

Calculates the uncovered expense balance following these rules:

• The expenses for the year can be covered with revenues from the 3 months following that year. Once covered, they go on to cover the expenses of the next year. In this case, the year is considered closed without any remaining expenses to cover. If they are not fully covered, the year will end with an uncovered balance, and starting from March 31st of that year, they accumulate into the expense balance for the next year.

This information would be grouped by Agent and displayed in tables with Agent and Date details, using a single-year slicer for selection. Additionally, there would be a summary at the year level.

The matrix, with a single-year slicer, should show:

• Gastos_Ac: Accumulated Expenses for the selected year up to the selected date.

• Ingresos_Ac: Accumulated Revenues for the selected year up to the selected date.

• Ingresos_Compensa: Revenues accumulated for the selected year, dedicated to compensating a negative balance from the previous year.

Here’s how I explain it:

• The previous year had both revenues and expenses. The difference between expenses and revenues is handled as follows:

  • If revenues are greater than expenses, the year closes "OK" and the balance is not considered.

  • If expenses exceed revenues, the difference stays as a compensation balance to be covered in the following year.

• This compensation happens only within the first 3 months (up to March 31st), as follows:

  • For example, in January, the previous year closed with a balance of 300 in uncovered expenses. We have 100 in current expenses and 200 in revenues. The remaining 100 of the previous year’s expenses and 100 of the current year’s expenses would be covered, totaling 200 in compensation revenues.

  • If in February there are no expenses and 200 in revenues, we will accumulate 300 in Ingresos Compensa and 100 in Ingresos_AC_Netos.

• Ingresos_AC_Netos: These are the revenues dedicated to compensating the Gastos_Ac of the current year. At most, this can be equal to Gastos_Ac, and it consists of Ingresos_AC_Netos for the selected year plus the Ingresos Compensa from the following year. (The Ingresos_AC_Netos include the Ingresos Compensa from the following year.)

Finally, the result will show the days of the year with values and the days of the next 3 months with Ingresos Compensa.

I am having trouble visualizing how to apply the DAX logic to achieve these measures, despite the simplicity of doing this in Excel.

Best regards,

[lbendlin · Accepted solution]

Thank you for providing the sample data.

I have bad news for you - Power BI/DAX does not support conditional aggregation.  The only function that supports it is List.Accumulate in Power Query.

You would also need to indicate what your starting date is.  Do we assume there is no debt carrying over from 2021?

[v-priyankata]

Hi @Carlos_Noob 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@lbendlin @Rufyda @Kedar_Pande @rodrigosan Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @Carlos_Noob 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[Rufyda · Accepted solution]

Hi,
Your calculation cannot be done in DAX because DAX does not support recursive or conditional accumulation.
A Power BI Super User confirmed this:

Power BI/DAX does not support conditional aggregation. Only Power Query (List.Accumulate) can do that.

To build your logic (uncovered expenses, 3-month compensation, year-to-year carryover), you must implement it in Power Query, not DAX.

Microsoft Learn Source

Power Query – List.Accumulate (supports recursion)

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Regards,
Rufyda Rahma | MIE 

[Carlos_Noob]

Hi, thanks for ur answers. Not sure if Ive replied correctly, perhaps this message appears twice, sorry in advance if thats the case. Ive tried something in the way RodrigoSan suggested. Ive not been able to get it works for more than the first year. Ive changed the name of the initial metrics to something more clear.

This is the link to weetranfer whit the excel with the expected results formulated and the pbix file with the metrics iv been able to get, (DAX Sheet "TryOne").

https://we.tl/t-Jo24IhI22a

This would be the data xample;

And this the expected results;

Thanks for your help
 

[lbendlin · Accepted solution]

Thank you for providing the sample data.

I have bad news for you - Power BI/DAX does not support conditional aggregation.  The only function that supports it is List.Accumulate in Power Query.

You would also need to indicate what your starting date is.  Do we assume there is no debt carrying over from 2021?

[Carlos_Noob]

Thanks for your response, Ibendlin.

Yes, in the first year of the data, we assume it as the starting point with a previous balance of 0 uncovered.I think it would be easy to implement with a recursive function in other languages or with Excel formulas, but I'm unable to figure it out in a Power BI context.

[rodrigosan]

Hi @Carlos_Noob .

I don’t know your data or your model, but with the help of your context and using an AI, I arrived at the measures below:

*** Important: You need a dCalendar table ***

Expenses = SUM(tAG[Despesas])
Revenues = SUM(tAG[Receitas])

Expenses_Ac =
// Accumulated expenses in the selected year (in the current row context - Agent, Date)
CALCULATE(
[Expenses],
DATESYTD('dCalendario'[Data])
)

Revenues_Ac =
// Accumulated revenues in the selected year (in the current row context - Agent, Date)
CALCULATE(
[Revenues],
DATESYTD('dCalendario'[Data])
)

Previous_Year_Pending_Balance =
// Calculates the negative balance (uncovered expenses) from the previous year.
// This value is always 0 or negative.
VAR SelectedYear = MAX('dCalendario'[Ano])

VAR ExpensesPrevYear =
CALCULATE(
[Expenses],
ALL('dCalendario'),
'dCalendario'[Ano] = SelectedYear - 1
)

VAR RevenuesPrevYear =
CALCULATE(
[Revenues],
ALL('dCalendario'),
'dCalendario'[Ano] = SelectedYear - 1
)

VAR BalancePrevYear = RevenuesPrevYear - ExpensesPrevYear

VAR UncoveredBalance =
// If the balance is negative (uncovered), returns the negative value.
MIN(0, BalancePrevYear)

RETURN UncoveredBalance

Compensating_Revenues =
// Calculates the accumulated revenues (up to March 31st of the selected year)
// used to cover the Previous_Year_Pending_Balance.
VAR PendingBalancePrevYear = [Previous_Year_Pending_Balance]

VAR RevenuesAcumulated3Months =
// Accumulated revenues in the selected year, limited to the first 3 months (Jan, Feb, Mar)
CALCULATE(
[Revenues],
DATESYTD('dCalendario'[Data]),
FILTER(
ALL('dCalendario'[Data], 'dCalendario'[Mês]), // Remove date context, but limit to first 3 months
'dCalendario'[Mês] <= 3
)
)

VAR CompensationAmount =
// The compensation value is the MINIMUM of:
// 1. Available revenues in the 3-month window.
// 2. The absolute value of the pending balance from the previous year.
MIN(
RevenuesAcumulated3Months,
ABS(PendingBalancePrevYear)
)

// Returns the value used for compensation (positive value)
RETURN CompensationAmount

Net_Revenues_Ac =
// Revenues dedicated to covering the current year's accumulated expenses (Expenses_Ac),
// after covering the previous year's pending balance.
VAR ExpensesAccumulatedCurrent = [Expenses_Ac]
VAR RevenuesAccumulatedCurrent = [Revenues_Ac]
VAR CompensatedValuePrevYear = [Compensating_Revenues]

VAR NetRevenueForCurrentExpenses = RevenuesAccumulatedCurrent - CompensatedValuePrevYear

VAR RevenueCompensatingCurrent =
// Net revenues used for current expenses, capped by the actual current accumulated expenses.
MIN(ExpensesAccumulatedCurrent, NetRevenueForCurrentExpenses)

RETURN RevenueCompensatingCurrent

Uncovered_Expenses_Final_Balance =
// Final balance of uncovered expenses: Accumulated expenses minus net revenues covering them.
[Expenses_Ac] - [Net_Revenues_Ac]

[Kedar_Pande]

Please share a simplified version of your PBIX file (in English) without sensitive data. You can upload it to a public cloud service like OneDrive, Google Drive, or Dropbox and share the link. This will help in understanding your data structure and the issue, allowing for more precise guidance.

[Praful_Potphode]

Hi @Carlos_Noob ,

as mentioned by @lbendlin , we need sample data or input/output screenshot to work with.

Thanks and Regards,

Praful

[Carlos_Noob]

Thanks for your answers, I've tryed this solution but i dont get the results. I have something similar but im only get the results for the first year. Ive changed some names to make it more clear. For example, first would be the sample data and, segond would be the expected results. I upload the excel with the formulas and what ive been able to archieve in the pbix file in wetransfer.

https://we.tl/t-Jo24IhI22a

Thanks for ur help

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...