Days Between Dates Issue

[Anonymous]

Hello,

I am attempting to calculate the days between two dates (using a calculated column), which is easy! However, I am returning incorrect values. See below screenshot:

For the selected "2022-02", I am being returned 31 days. I would like to return days passed in January, as well as the month selected. Not just January. So, this should return 31 + 28 = 59 days for January + February.

Any tips/tricks on adjusting my formula to accomodate?

Thanks,

Joel

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can solve your problem with measure, mainly because you need to calculate the second date.

Maesure:

Days between date =
DATEDIFF (
    DATE ( YEAR ( TODAY () ), 1, 1 ),
    DATE ( YEAR ( TODAY () ), SELECTEDVALUE ( 'Date'[Date].[MonthNo] ) + 1, 1 ) - 1,
    DAY
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Yup! That'll work. Thanks much.

[Greg_Deckler]

@Anonymous For the first parameter, use DATE(2022,1,1). Also you can get the number of days between 2 dates using simple subtraction:

([date1] - [date2]) * 1.

[Anonymous]

Hey Greg,

Thanks for the tips, but that doesn't quite solve my current predicament. See below screenshot:

When I select 2022-02 on my slicer, I want to see days passed between January 1 and Februrary 28th (59 days total). Currently, when I select 2022-02, I only get January's days (31). I want January + February (59 days).

Thanks,

Joel