Difference between start date and end date

[GS10]

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

[ChiragGarg2512 · Accepted solution]

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.

[v-zhouwen-msft · Accepted solution]

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.

[ChiragGarg2512 · Accepted solution]

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.

[_AAndrade]

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?