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 s...
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.

     

     

     

     

     

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