Number of days between min and max dates of single column

[sammi1244]

I have a single date column where I need to get the total number of days between the min and max date in that column. I have tried several formulas but nothing seems to get the correct result.  I need a function or calculation that gives NUMBER OF DAYS BETWEEN-- I tried DATESBETWEEN, DATESINPERIOD, etc. but they all return a date and not a number of days. This is what I have guessed (don't laugh ‌‌😋) so far:  

Days In Period = **insert function**(MIN('HP Evo Power BI Data'[DATE ENTERED].[Date],MAX('HP Evo Power BI Data'[DATE ENTERED].[Date])))

Thanks --Sam

[Ashish_Mathur]

Hi,

Write this as a calculated column formula in yourHP Evo Power BI Data table

=1*(MAX('HP Evo Power BI Data'[DATE ENTERED])-MIN('HP Evo Power BI Data'[DATE ENTERED]))

Hope this helps.

[sammi1244]

Thank you! I just prefered to not create a new column so I went with @KNP suggestion. Thanks for chiming in. I apprecate it.

[KNP · Accepted solution]

Hi @sammi1244, 

DAX is not really my specialty but I think this will work.

(change field names as required obviously)

Measure = 
    VAR MinDate = MIN(financials[Date]) 
    VAR MaxDate = MAX(financials[Date]) 
RETURN 
    DATEDIFF(MinDate, MaxDate, DAY)

Hope this helps.

Kim

[sammi1244]

So this is perfect; exactly what I want.  'Key Measures/Calculation'[Days In Period]' is the measure we created and it is used in my next measure:  

Avg Days in AR = (AVERAGE('HP Evo Power BI Data'[TRANSACTION AMOUNT])/ sum('HP Evo Power BI Data'[Total Payments Amount]) * 'Key Measures/Calculation'[Days In Period])

But this results in 0 (zero). Anyone with feedback on what I did wrong? 

[KNP]

Glad that first part worked.

It would probably be easier to see the issue if you could post a sample dataset but I would probably start by turning the [TRANSACTION AMOUNT] and [Total Payments Amount] into measures and see if they return what is expected. It's always easier to debug DAX (or any) issues in smaller bits.

Also, best to use DIVIDE instead of '/' as it can deal with divide by zero issues and allows you to pass an alternate value. Other than that, without seeing the data and expected output it is difficult to offer any more than that.