topic Re: I need to create a formula, but I don't know how in DAX Commands and Tips

I need to create a formula, but I don't know how

Guilherme587171 — Wed, 12 Feb 2025 19:50:25 GMT

"Let's go, I have the inclusion date of the amount and the due date in the same table, and I need to include the included amount, the due amount, and a division that returns the percentage overdue in the same table. But in all the ways I've tried, it results in some conflict in the table and does not return the correct data...

Does anyone have any suggestions on how to solve this?

Re: I need to create a formula, but I don't know how

Anonymous — Thu, 13 Feb 2025 02:47:05 GMT

Hi Guilherme587171 ,

It would be nice to share an example and give the expected output.
I'm assuming you have a table like this:

I created a calendar table and created relationships:

Then please create these measures:

ShowValueForDates = VAR __last_date_with_data = CALCULATE(MAX('Table'[Due Date]),REMOVEFILTERS()) VAR __first_date_visible = MIN('Date'[Date]) VAR __result = __first_date_visible <= MIN(__last_date_with_data, TODAY()) RETURN __result

OverDue Count = VAR __today = TODAY () VAR __overdue_count = IF ( [ShowValueForDates], CALCULATE ( COUNTROWS ( 'Table' ), ( 'Table'[Date completed] = BLANK () && 'Table'[Due Date] <= __today ) || ( 'Table'[Date completed] > 'Table'[Due Date] ) ) ) RETURN __overdue_count

Count ALL = COUNTROWS('Table')

Percentage Overdue = DIVIDE([OverDue Count],[Count ALL])

Cumulative Percentage Overdue = VAR __cumulative_overdue_count = IF([ShowValueForDates], CALCULATE([OverDue Count],FILTER(ALL('Table'),'Table'[Due Date]<=MAX('Date'[Date])))) VAR __cumulative_count_all = IF([ShowValueForDates], CALCULATE([Count ALL],FILTER(ALL('Table'),'Table'[Due Date]<=MAX('Date'[Date])))) RETURN DIVIDE(__cumulative_overdue_count,__cumulative_count_all)

[OverDue Amount] Similar to the above.

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: I need to create a formula, but I don't know how

Poojara_D12 — Thu, 13 Feb 2025 05:37:54 GMT

Hi Guilherme587171

To calculate the included amount, due amount, and overdue percentage in the same table without conflicts, you need to create separate DAX measures for each value. The included amount should sum values based on the inclusion date, while the due amount should sum values based on the due date. The overdue percentage is calculated as the ratio of the overdue amount to the total due amount. A key challenge is ensuring that date filters do not interfere with calculations, which can be managed using the CALCULATE() function with REMOVEFILTERS() or ALL() on the appropriate date column. Additionally, ensure that relationships between tables (if any) do not filter out relevant data unexpectedly. If issues persist, validating the data model structure and testing individual measures separately can help pinpoint errors.