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 ship date and actual shipped date. If the actual shipped date is later, then the order was past due.

 

Thank you 

 

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

5 Replies

  • NDG's avatar
    NDG
    Frequent Visitor
    4 years ago

    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

  • 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