Forum Discussion

AMBP1973's avatar
AMBP1973
Icon for Helper III rankHelper III
2 years ago

Calculate Date Status

Hi,

 

I was wondering if someone could please assist with the following. 

 

I currently have the following DAX to calculate no. of documents overdue :

Document Total Overdue = COUNTROWS(FILTER('E&T Document Status Tracker',NOT(ISBLANK('E&T Document Status Tracker'[Cancellation Date]))&&'E&T Document Status Tracker'[Cancellation Date]<TODAY()))
 
I also have another column, which indicates the actual date a document has been approved:
'E&T Document Status Tracker'[Approval Date] 
 
I would like to show no. of documents overdue at any stage over a period of time i.e. no. in Jul 2023 vs no. in Jul 24. I am thinking where an approval date (or no approval date) has occured after the 'cancellation date'? Could someone please assist with an appropriate measure to calcultae this, and if possible indictae how I can show the status with a date slicer and variance between a date period?
 
Any assistance would be greatly appreciated.
 
Regards,
AM

4 Replies

  • shafiz_p's avatar
    shafiz_p
    Icon for Super User rankSuper User
    2 years ago

    Hi, AMBP1973  As I can understand, that you want to calculate number of overdue document and compare with in various time point. If this is the case, I want to share my point of view.

    You need 3 column in your table, document submission date, due date, and approval date. If any document approved after due date will be considered over due. You can use filter function to evaluate this as you have done already. 

    Another case is, if any document not approved yet and current date passed the due date will be considered overdue.

    Once you evaluate no of overdue document, then you can compare between various time point.

    Hope this helps!!

    If this solved your problem, please mark it as a solution!!

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    AMBP1973 , To calculate number of documents overdue you can use measure

     

    First make sure you have a date table

    Then
    Documents Overdue =
    CALCULATE(
    COUNTROWS('E&T Document Status Tracker'),
    FILTER(
    'E&T Document Status Tracker',
    NOT(ISBLANK('E&T Document Status Tracker'[Cancellation Date])) &&
    'E&T Document Status Tracker'[Cancellation Date] < TODAY() &&
    (
    ISBLANK('E&T Document Status Tracker'[Approval Date]) ||
    'E&T Document Status Tracker'[Approval Date] > 'E&T Document Status Tracker'[Cancellation Date]
    )
    )
    )

     

    To calculate variance you can create another measure

     

    Documents Overdue Current Month =
    CALCULATE(
    [Documents Overdue],
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -1, MONTH)
    )

    Documents Overdue Previous Month =
    CALCULATE(
    [Documents Overdue],
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]) - 1, -1, MONTH)
    )

    Variance =
    [Documents Overdue Current Month] - [Documents Overdue Previous Month]

    • AMBP1973's avatar
      AMBP1973
      Icon for Helper III rankHelper III
      2 years ago

      Hello bhanu_gautam ,

       

      Thankyou for the measures you have provided, much appreciated. I created a Date Table = CALENDARAUTO() to include in these, however I am not getting any results in any visuals or a table?

       

      However, I am not 

      • AMBP1973's avatar
        AMBP1973
        Icon for Helper III rankHelper III
        2 years ago

        Apologies, seems as though the message didn't come through properly.

         

        Further to the above, I am not getting any results in any visuals or a table? Here is the measures I have created with the above date table:

        Documents Overdue Current Month =
        CALCULATE(
        [Documents Overdue],
        DATESINPERIOD('Date Table'[Date].[Date], MAX('Date Table'[Date]), -1, MONTH)
        )
         
        Documents Overdue Previous Month =
        CALCULATE(
        [Documents Overdue],
        DATESINPERIOD('Date Table'[Date], MAX('Date Table'[Date]) - 1, -1, YEAR)
        )
         
        Variance =
        [Documents Overdue Current Month] - [Documents Overdue Previous Month]
         
         
        Could you please assist further? Many thanks.