Forum Discussion

Frelon's avatar
Frelon
Frequent Visitor
4 years ago
Solved

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 IDStartDateEndDateExpected result for calculated duration (in days)
16th of January 202110th of January 20215
230th 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!

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    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's avatar
      Frelon
      Frequent Visitor
      4 years ago

      Thank you very much