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...
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.