count days between two dates

[Anonymous]

How can I count the days between two dates which are defined as whole number i tabular and not as date in a dax measure?

For example:

firts date 20201201

last date 20210201

firstdate-lastdate= 63 days

I can't change the data type in tabular , there are other measures using this column.

[selimovd · Accepted solution]

Hello @Anonymous ,

you can transform the integer into a date and then calculate the difference with the DATEDIFF function:

Date Diff =
VAR vFistDate = DATE( LEFT( myTable[first date], 4 ), MID( myTable[first date], 5, 2 ), RIGHT( myTable[first date], 2 ) )
VAR vLastDate = DATE( LEFT( myTable[last date], 4 ), MID( myTable[last date], 5, 2 ), RIGHT( myTable[last date], 2 ) )
VAR vDiff = DATEDIFF( vFistDate, vLastDate, DAY )
RETURN
    vDiff

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

Thank you @selimovd , I tested the measure and it works but not when the first and last day is the same day.

for example :

2019-05-14    2019-05-14    vDiff=0   (but it should be counted as 1 day, vDiff=1)

[selimovd]

Hey @Anonymous ,

mathematically there is not a single day difference between today and today.

But if you want to count that as one day, then you should add +1 for all values to be consistent.

Then try:

Date Diff =
VAR vFistDate = DATE( LEFT( myTable[first date], 4 ), MID( myTable[first date], 5, 2 ), RIGHT( myTable[first date], 2 ) )
VAR vLastDate = DATE( LEFT( myTable[last date], 4 ), MID( myTable[last date], 5, 2 ), RIGHT( myTable[last date], 2 ) )
VAR vDiff = DATEDIFF( vFistDate, vLastDate, DAY )
RETURN
    vDiff + 1

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi