How to create past due values

[NDG]

Good day, 

 I want to create a history past due report. I don't have what orders are past due not as a measure or column. I will need a DAX calculation per month and year based on the scheduled ship date and actual shipped date. If the actual shipped date is later, then the order was past due.

Thank you 

[v-jingzhang]

Hi @NDG 

Can you provide some sample data? It will help us understand your requirement better. I have a few questions currently. Does every row represent an order in your data table? Do you want to count how many orders are past due per month/year or something else? Is your table similar to below?

OrderID	Scheduled Ship Date	Actual Ship Date	...
1	2022-1-1	2022-1-5	...
2	2022-2-1	2022-1-25	...
...	...	...	...

Best Regards,
Community Support Team _ Jing

[NDG]

Hi

Sure I attached excel file that is similar to what I need. I can’t share the BI report. To point will need DAX as I can’t modify the tables. As for your questions:

• Correct every row is containing info for a single order
• Will need to have $ value of past due
• Attached is sample date

Other words to have end of month trigger where when used to provide open lines not shipped with scheduled shipped date prior ending month (in the past)  

I hope this helps

Thank you

[v-jingzhang · Accepted solution]

Hi @NDG 

It is a Pivot table. You can use Matrix visual to achieve a similar result. But can you provide some dummy data to show the table structure of original data? And how many tables are used in this model? At least we need to know which columns are from which tables and what relationships are between them. 

For example, if Scheduled Ship Date and Actual Ship Date are from the same table, you can try below measure. But it is based on my assumptions and it doesn't consider Credited Plants. Without data, it's difficult to write a DAX formula which is appropriate to a model. 

Past Due Total =
CALCULATE (
    SUM ( 'Table'[$ value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Scheduled Ship Date] < 'Table'[Actual Ship Date]
            || (
                ISBLANK ( 'Table'[Actual Ship Date] )
                    && MAX ( 'Date'[Date] ) > 'Table'[Scheduled Ship Date]
            )
    )
)

Regards,

Jing

[NDG]

I apologize. I have the history of open orders and will need DAX function (as I can’t edit tables) to provide snapshot by month past due history. Now I have a history of open orders but not the past due value by months. I was wondering what DAX to use to have as below.

My guess is value by month past due to be Scheduled ship date  < Actual ship date or blank (if order is not shipped yet)

Sorry I cannot share the BI file

[mh2587]

To get quick answer follow below link

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...