Some difficulties with a date calculation...

[Frelon]

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!

[v-zhangti · Accepted solution]

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.

[Frelon]

Thank you very much v-zhangti , it works exactly as expected 🙂