cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Some difficulties with a date calculation...

Hi all,

I have some difficulties with a date calculation.
I have a table with 3 columns, "Event ID", "StartDate" and "EndDate".
I use this table in a visual where the user has a date slicer to select the period to examinate. This date slicer is based on a calendar table (not linked to the first table).
The need is simply to mesure the duration of all events within the period selected with the date slicer.
The problem is that I have events w/o EndDate and for these events, I would like the duration to be calculated by using the end date of the Date slicer as "EndDate".
As an example, let's say the user selects the period 1st of January 2021 to 31st of January 2021.

 Event ID StartDate EndDate Expected result for calculated duration (in days) 1 6th of January 2021 10th of January 2021 5 2 30th of January 2021 (empty) 2

Since there is no EndDate for the second event, I would like to take the end date of the period selected with the date slicer and use it for the calculation (31/01/2021 - 30/01/2021 = 2 days).

Since the DAx formula MAX(MyCalendar[Date]) gives me the end date of the period selected in the slicer, I tried following DAX formula:

Duration =
IF (
'Table1'[EndDate] <> BLANK (),
DATEDIFF('Table1'[StartDate], 'Table1'[EndDate], DAY)+1,
DATEDIFF('Table1'[StartDate], MAX(MyCalendar[Date]),DAY)+1
)

But it does not work. This formula takes as end date the last day of MyCalendar table and not the one selected in the slicer...

Could someone help me in solving this problem?

Thank you!

1 ACCEPTED SOLUTION
Community Support

Hi, @Frelon

You can try to solve this problem with measure.

1. First create a calendar table.

``````Date =
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 2, 28 ) )
``````

2. Use IF function and DATEDIFF function to write Measure.

``````Duration =
DATEDIFF (
MAX ( 'Table'[StartDate] ),
IF (
MAX ( 'Table'[EndDate] ) <> BLANK (),
MAX ( 'Table'[EndDate] ),
MAX ( 'Date'[Date] )
),
DAY
) + 1
``````

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.

2 REPLIES 2
Community Support

Hi, @Frelon

You can try to solve this problem with measure.

1. First create a calendar table.

``````Date =
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 2, 28 ) )
``````

2. Use IF function and DATEDIFF function to write Measure.

``````Duration =
DATEDIFF (
MAX ( 'Table'[StartDate] ),
IF (
MAX ( 'Table'[EndDate] ) <> BLANK (),
MAX ( 'Table'[EndDate] ),
MAX ( 'Date'[Date] )
),
DAY
) + 1
``````

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.

Frequent Visitor

Thank you very much

Announcements

#### 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 Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors