30,60,90 day filter | Calculate Days Pending Until Due Date

[Anonymous]

I'm working with a 30,60,90 day filter. I want to calculate days between today and 'Task Next Milestone Due Date'. I tried DATEDIFF but it didn't work in my equation. My equation is below:

Days Pending =

CALCULATE (

MAX ( 'Table'[Task Next Milestone Due Date] ),

FILTER (

'Table',

'Table'[Task Next Milestone Due Date]

<= TODAY ()

+ SELECTEDVALUE (

'Days Pending'[Days Pending],

MAX ( 'Days Pending'[Days Pending] )

)

&& 'Table'[Task Next Milestone Due Date] >= TODAY ()

)

)

Currently Days Pending shows just the date. As below. How can I display the difference between the due date and today using a similar formula?

 

[v-deddai1-msft]

Hi @Anonymous ，

Would you please refer to the following measure:

Days Pending =
VAR _duedate =
    SELECTEDVALUE (
        'Days Pending'[Days Pending],
        MAX ( 'Days Pending'[Days Pending] )
    )
        + MAX ( 'Table'[Date] )
RETURN
    DATEDIFF ( TODAY (), _duedate, DAY )

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXPAL7FZtyhIrUpig3P3LroBYtrHtTjQfeK_IxnecEUoNQ?e=gGJjDZ

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@Anonymous ,

Have you tried

a column

datediff(Today(),'Table'[Task Next Milestone Due Date], day())

Measure

datediff(Today(),max('Table'[Task Next Milestone Due Date]), day())

[Anonymous]

Tried this option but it did not work.

[v-deddai1-msft]

Hi @Anonymous ,

In your formula , you are calculating MAX('Table'[Task Next Milestone Due Date]) for Days Pending, so the date type of Days Pending would be same as  [Task Next Milestone Due Date], and you already have [Days Pending] in 'Days Pending' table. So what you want to implement with your formula doesn't seem to have anything to do with what you call the measure(or column).

Would you please show us some sample data and your expected output or your error with use of datediff? Then we will help you more correctly.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai