Forum Discussion

veerub's avatar
veerub
Frequent Visitor
5 years ago
Solved

Calculating Remaining Days from last reporting date

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...
  • Nathaniel_C's avatar
    Nathaniel_C
    5 years ago

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