Date Difference Calculation between dates

[Vikas_Mattela]

Hello Community,

I have a table with Start Date and End Date. I would like to calculate Date Diff based on Start Date and End Date Column.

For example my start date is "10/25/2022" and end date is "02/27/2023". Now in this scenario how can I calculate days in 2022 and in 2023 only. Please find screenshot for your reference.

[Ashish_Mathur]

Hi,

Please find attached the PBI file.

Hope this helps.

[123abc]

You can calculate the date difference between two dates in Excel by using a formula. In your case, you want to calculate the number of days in 2022 and 2023 separately. Here's how you can do it:

Assuming your Start Date is in cell A2 and your End Date is in cell B2, you can use the following formula to calculate the number of days in 2022:

=IF(YEAR(B2)=2022, B2-A2+1, IF(YEAR(A2)=2022, DATE(2022,12,31)-A2+1, 0))

And for the number of days in 2023:

=IF(YEAR(B2)=2023, B2-DATE(2023,1,1)+1, IF(YEAR(A2)=2023, B2-DATE(2023,1,1)+1, 0))

Here's how these formulas work:

1. YEAR(B2) and YEAR(A2) extract the year from the End Date and Start Date respectively.

2. If the End Date (B2) is in 2022, it calculates the difference between the End Date and Start Date in 2022 and adds 1 to include both the start and end dates.

3. If the Start Date (A2) is in 2022, it calculates the difference between December 31, 2022, and the Start Date and adds 1.

4. Similarly, for 2023, it calculates the difference between the End Date and January 1, 2023, and adds 1 if the End Date is in 2023. It also calculates the difference between the End Date and January 1, 2023, and adds 1 if the Start Date is in 2023.

These formulas will give you the number of days in 2022 and 2023 separately based on your Start Date and End Date columns. You can enter these formulas in separate cells to get the results for each year.

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

[123abc]

In Power BI, you can calculate the date difference between the Start Date and End Date columns, and then filter the result to only include days in 2022 and 2023. You can use the following steps to achieve this:

1. Create a Calculated Column: First, you'll need to create a calculated column that calculates the date difference between the Start Date and End Date columns. You can do this by using the DATEDIFF function. Assuming your table is named "YourTable," and the calculated column name is "DateDiff," the formula would look like this:

DateDiff = DATEDIFF(YourTable[Start Date], YourTable[End Date], DAY)

1. This formula calculates the difference between the Start Date and End Date in days.

2. Create a Filter: Next, you can create a filter to include only the days in 2022 and 2023. You can use a measure for this purpose. Create a new measure with the following formula:

DaysIn2022And2023 =
CALCULATE(
SUM(YourTable[DateDiff]),
FILTER(
YourTable,
YEAR(YourTable[Start Date]) IN {2022, 2023}
)
)

1. This measure sums the DateDiff column but filters it to only include rows where the Start Date year is 2022 or 2023.

2. Display the Result: Finally, you can display the "DaysIn2022And2023" measure in your Power BI report to see the total number of days in 2022 and 2023 for each row in your table.

Make sure to replace "YourTable," "Start Date," and "End Date" with the actual names of your table and columns in your Power BI dataset. This approach will give you the desired date difference calculation for the specified years.

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