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

[Guilherme587171]

"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?

[Anonymous · Accepted solution]

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

[Poojara_D12 · Accepted solution]

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.

[Anonymous · Accepted solution]

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