Forum Discussion

NDG's avatar
NDG
Frequent Visitor
4 years ago
Solved

How to create past due values

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 s...
Need Help
Show and Tell
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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

v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

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's avatar
NDG
Frequent Visitor
4 years ago

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's avatar
    v-jingzhang
    Community Support
    4 years ago

    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