Forum Discussion

GS10's avatar
GS10
Frequent Visitor
2 years ago
Solved

Difference between start date and end date

Hi,

Consider my slicer is march.
case 1:my start time is in february and end time is in march so I want to exclude the start time which is feb and I consider 1st of march as start time and do the difference.
case 2:my start time and end time falls in the slicer I have to find the difference.

case 3:if my end time is blank and start time is in february then I have to find the duration betwen the 1st of march to current time.

Regards,

GS

 

Need Help
  • ChiragGarg2512's avatar
    ChiragGarg2512
    2 years ago

    For Case 1:

    If start date = month(today()) - 1: use TotalMTD function

    For Case 2: 

    If the start date and end date are both part of the same month simply the difference in the dates can work.

     

    For case 3:[Bit unclear]

    This should go similarly to case 1 by using TotalMTD from 1st March till date.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ChiragGarg2512 , _AAndrade ,thank you for your replies, I'll add further.

    Hi GS10 ,

    The Table data is shown below:

    Please follow these steps:
    1. Use the following DAX expression to create a table

     

    Table 2 = CALENDAR(DATE(2024,3,1),DATE(2024,3,31))

     

    2.Creating table-to-table relationships

    3.Use the following DAX expression to create a measure

     

    Duration =
VAR _a =
    SELECTEDVALUE ( 'Table'[Start Date] )
VAR _b =
    SELECTEDVALUE ( 'Table'[End Date] )
VAR _c =
    MIN ( 'Table 2'[Date] )
VAR _d =
    MAX ( 'Table 2'[Date] )
VAR _e =
    IF (
        MONTH ( _a ) = 2
            && MONTH ( _b ) = 3,
        DATEDIFF ( DATE ( 2024, 3, 1 ), _b, DAY ),
        IF (
            _a >= _c
                && _b <= _d,
            DATEDIFF ( _a, _b, DAY ),
            IF (
                _b = BLANK ()
                    && CALCULATE ( MONTH ( MAX ( 'Table'[Start Date] ) ) = 2, ALL ( 'Table 2' ) ),
                DATEDIFF (
                    CALCULATE ( MAX ( 'Table'[Start Date] ), ALL ( 'Table 2' ) ),
                    DATE ( 2024, 3, 1 ),
                    DAY
                )
            )
        )
    )
RETURN
    _e

     

    4.Final output

     

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    Hi GS10,

    I didn't understand your case 3. Could you please explain in more detail what you want on this case?
    And what you're looking for is the Month duration like you have on the picture?

  • ChiragGarg2512's avatar
    ChiragGarg2512
    Solution Sage
    2 years ago

    For Case 1:

    If start date = month(today()) - 1: use TotalMTD function

    For Case 2: 

    If the start date and end date are both part of the same month simply the difference in the dates can work.

     

    For case 3:[Bit unclear]

    This should go similarly to case 1 by using TotalMTD from 1st March till date.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ChiragGarg2512 , _AAndrade ,thank you for your replies, I'll add further.

    Hi GS10 ,

    The Table data is shown below:

    Please follow these steps:
    1. Use the following DAX expression to create a table

     

    Table 2 = CALENDAR(DATE(2024,3,1),DATE(2024,3,31))

     

    2.Creating table-to-table relationships

    3.Use the following DAX expression to create a measure

     

    Duration =
VAR _a =
    SELECTEDVALUE ( 'Table'[Start Date] )
VAR _b =
    SELECTEDVALUE ( 'Table'[End Date] )
VAR _c =
    MIN ( 'Table 2'[Date] )
VAR _d =
    MAX ( 'Table 2'[Date] )
VAR _e =
    IF (
        MONTH ( _a ) = 2
            && MONTH ( _b ) = 3,
        DATEDIFF ( DATE ( 2024, 3, 1 ), _b, DAY ),
        IF (
            _a >= _c
                && _b <= _d,
            DATEDIFF ( _a, _b, DAY ),
            IF (
                _b = BLANK ()
                    && CALCULATE ( MONTH ( MAX ( 'Table'[Start Date] ) ) = 2, ALL ( 'Table 2' ) ),
                DATEDIFF (
                    CALCULATE ( MAX ( 'Table'[Start Date] ), ALL ( 'Table 2' ) ),
                    DATE ( 2024, 3, 1 ),
                    DAY
                )
            )
        )
    )
RETURN
    _e

     

    4.Final output

     

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.