Forum Discussion

manideep547's avatar
manideep547
Helper III
6 years ago

Transferred

Hi all, I have Date slicer If the person is in destination 11 before the minimum date of the slicer That means let consider Id 11 is present in destination 1 in before the minimum date of the slic...
Need Help
v-lionel-msft's avatar
v-lionel-msft
Community Support
6 years ago

Hi manideep547 ,

 

Maybe you can refer these formula:

 

Column 2 = 
VAR x = 
CALCULATE(
    DISTINCTCOUNT([ID]),
    ALLEXCEPT(
        Sheet4,
        Sheet4[DESTINATION]
    )
)
VAR y = 
CALCULATE(
    COUNT([ID]),
    ALLEXCEPT(
        Sheet4,
        Sheet4[DESTINATION]
    )
)
RETURN
y - x

Column 5 = 
IF(
    [Column 2] = 0,
    [DESTINATION] & "-" & [DESTINATION] 
)

Column = 
VAR x = 
CALCULATE(
    COUNT(Sheet4[DESTINATION]),
    ALLEXCEPT(
        Sheet4,
        Sheet4[ID]
    )
)
RETURN
IF(
    x >1,
    x/2
)

Column 6 = 
VAR x = 
CALCULATE(
    MAX([DESTINATION]),
    ALL(Sheet4)
)
RETURN
IF(
    [Column] <> 0,
    IF(
        [DESTINATION] = x,
        x & "-" & MIN([DESTINATION]),
        [DESTINATION] & "-" & x
    )
)

 

 

Best regards,
Lionel Chen

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