Calculating Remaining Days from last reporting date

[veerub]

Hi All,

I need some help to solve the following

I have a table with a column maturity date. The maturity date column includes both (i) dates which are before today's date (ii) dates which are after today's date.

I want to have a new column which give me the remaining days from a last reporting date (which is not today). The last reporting date to be source from another table such as in below case should be 28-May-21

How should i go about it or if you guys have an easier way to do it?

Regards

VB

[Nathaniel_C]

Hi @veerub , or if what you are looking at is zero if bond has matured, and the remaing time from today until maturity, try this.

RemainingDays = 
VAR _today =
    TODAY () //This measure returns 0 if matured else number of remaining dayes
    
    VAR _reportDate = [ReportDate]
VAR _bondMaturity =
    MAX ( Maturity[MaturityDate] )
VAR _dateDiff =
    DATEDIFF (
        _today,_bondMaturity,
         
        DAY
    )
RETURN
   If(_bondMaturity>_today, _dateDiff,0)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @veerub , not sure if I understand your final outcome, but try this:

DateCount =
VAR _today =
    TODAY () //This measure returns the count from last reporting date to maturity, but if the bond has already matured, returns the diff from last reporting date to maturity date
VAR _reportDate = [ReportDate]
VAR _bondMaturity =
    MAX ( Maturity[MaturityDate] )
VAR _dateDiff =
    DATEDIFF (
        _reportDate,
        IF ( _today > _bondMaturity, _today, _bondMaturity ),
        DAY
    )
RETURN
    _dateDiff

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

[veerub]

Hi Nathaniel,

Thanks for the response. Here are some additional comments

1. If maturity date < reporting date, it shall return the negative values. that is maturity date minus reporting date.

2. The 57 in your example above is correct. But how did you bring the reportingdate into the model? the reporting date will be a single value. What would be the most appropriate way to get the reportDate updated

Regards,

Veeru

[Nathaniel_C · Accepted solution]

Hi @veerub , just so we are clear Remaining Days calculates the remaining days from today, and the Date Count calculates from the reporting date to the maturity.  Added some dax to make a negative number if the report date exceeds maturity. Changed the date to 6/3/2021 so that it exceeds the maturity date in one instance.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

DateCount = 
VAR _today =
    TODAY () //This measure returns the count from last reporting date to maturity, but if the bond has already matured, returns the diff from last reporting date to maturity date
VAR _reportDate = [ReportDate]
VAR _bondMaturity =
    MAX ( Maturity[MaturityDate] )
VAR _dateDiff =
    DATEDIFF (
        _reportDate,
        IF ( _today > _bondMaturity, _today, _bondMaturity ),
        DAY
    )

    
var _reverseDateDiff = DATEDIFF(_reportDate,_bondMaturity,DAY)
RETURN
   If(_bondMaturity>_today, _dateDiff,if(_bondMaturity<_reportDate,_reverseDateDiff))

=======================================================================

RemainingDays = 
VAR _today =
    TODAY () //This measure returns 0 if matured else number of remaining dayes
    
    VAR _reportDate = [ReportDate]
VAR _bondMaturity =
    MAX ( Maturity[MaturityDate] )
VAR _dateDiff =
    DATEDIFF (
        _today,_bondMaturity,
         
        DAY
    )

var _reverseDateDiff = DATEDIFF(_reportDate,_bondMaturity,DAY)
RETURN
   If(_bondMaturity>_today, _dateDiff,if(_bondMaturity<_reportDate,_reverseDateDiff))

[veerub]

Hi Nathaniel,

Remaining Days calculates the remaining days from reporting date (which represent the DateCount in your example).

The utimate objective is to bucket these remaining days into the following buckets

1. Matured ( all negative values)

2. 0-1 month (0 to 30 days)

3. 3-6 months (31 to 180 days)

4. 6-12 months (181 to 365 days)

5. Above 1 year (greater than 365 days)

Can you please share your pbix file. Thanks

[Nathaniel_C]

I just built a table with the date in it.  It justs needs to be a separate table.  In your world, where would you get that date?
So in the picture below except for the -numbers, is DateCount, or Remaining Days, the expected outcome.

[veerub]

The reporting date will change on daily basis and will need to be updated by user. What would be the most appropriate way to implement it? Can this be done via an excel?