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

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