Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
julesdude
Post Partisan
Post Partisan

DATEDIFF incorrect - not sure why

Hi all,

I have the following matrix table in my report:

julesdude_0-1680079119596.png

The Unexpired Lease in Days column contains the following:

 

 

Unexpired Lease in Days =
VAR _asofdate = [As Of Date]
VAR _leaseenddate = [End Lease Date]
RETURN
DATEDIFF(_asofdate, _leaseenddate, DAY)

 

 

However, while this provides the correct result in some instances, in others it does not. For example, the date difference between the as of date column and End Lease Date column in the above top row is giving me 6948. When I run these dates in a quick DATEDIF formula in Excel it is giving me a higher figure of 7153

What is the issue here? Surely it should be a simple case of referring to the measure that is creating the value for [End Lease Date], and the same with {As Of Date] and then that works for each row.

[as of date] is the selection of a date from a date picker filter in the report

[End Lease Date] is a more extensive measure that returns a date based on certain criteria, the last line being:

RETURN

FORMAT(_targetdate, "dd/mm/yyyy")

This shouldn't be a problem as the measure calculating the date diff above should simply lift this measure providing the date, and use this in its arguments along with the as of date measure also lifted.

Where am I going wrong?

1 ACCEPTED SOLUTION

@julesdude 

Not sure the reason for using the FORMAT function. However, please try

RETURN
_targetdate


Or


RETURN
DATEVALUE(FORMAT(_targetdate, "dd/mm/yyyy"))

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

Hi @julesdude 
Make sure [End Lease Date] is datetime data type not text. Look like it is aligned left as and indication of a text (dates and numbers are usually aligned right)

Hi @tamerj1 

Thanks. Yes I think you are right and this is the issue. However when I select the measure [End Lease Date] so that I can change the Format dropdown on the Ribbon to Date, I only have Text as an option and cannot change this.
How can I do so? The measure in full is:

End Lease Date = 
VAR _asOfDate = [As Of Date]
VAR _assetref = [asset reference]
VAR _leaseref = [Lease Reference]
VAR _unitref = MAX(Unit[Unit Reference])
VAR _tenantref = [Tenant Reference]
VAR _datein3months = EDATE([As Of Date],3)
RETURN
VAR _filter = 
FILTER(Lease_Unit,
     MAX(Lease_Unit[Lease.Tenant Reference]) = _tenantref &&
        AND (
            Lease_Unit[Lease.Expiration Date] >= _asOfDate,
            Lease_Unit[Lease.Commencement Date] <= _asOfDate
        )
            || Lease_Unit[Lease.Lease Status] IN { "Holding Over", "Month-to-Month" }
        && OR( 
            Lease_Unit[Lease.Termination Date] >= _asOfDate, 
        isblank(Lease_Unit[Lease.Termination Date])
        )
     )
VAR _terminationDate = CALCULATE(MAX(Lease_Unit[Lease.Termination Date]),_filter)
VAR _expirationdate = CALCULATE(MAX(Lease_Unit[Lease.Expiration Date]),_filter)
VAR _status = CALCULATE(MAX(Lease_Unit[Lease.Lease Status]),_filter)

VAR _targetdate = IF(NOT ISBLANK(_terminationDate), _terminationDate,  
                    IF(_status = "Holding Over" || _status = "Month-to-Month", _datein3months,
                    IF(NOT ISBLANK(_expirationdate), _expirationdate, 
                    _datein3months))) 

RETURN

FORMAT(_targetdate, "dd/mm/yyyy")

I had to put the FORMAT part at the end as it wasn't recognising anything as a date?

@julesdude 

Not sure the reason for using the FORMAT function. However, please try

RETURN
_targetdate


Or


RETURN
DATEVALUE(FORMAT(_targetdate, "dd/mm/yyyy"))

Thanks @tamerj1 

It worked simply with replacing the FORMAT line as per your suggestion, and just use:

RETURN

_targetdate

It is really strange - I had put this in originally because I could not select the Format of the measure as a date at the time. but it now gives me the option to Format as a date now that I've removed the FORMAT line. 
Anyway, many thanks for your help! The format change now provides the correct calculations:

julesdude_0-1680083407085.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.