CODEWITHSUNDEEP
powerbidax
Sweepster
Sweepster

Reputation: 1949

Power BI measure how to determine which date range applies if there is overlap?

I have a measure as follows:

    Measure = 
VAR CurrentContractDate = SELECTEDVALUE( sales[Date] )

RETURN
    IF(
        NOT ISBLANK( CurrentContractDate ),
        CALCULATE( 
            SELECTEDVALUE( 'Employees'[Office Name] ), 
            FILTER( 'Employees', 'Employees'[Contract Start] <= CurrentContractDate && 'Employees'[Contract End] >= CurrentContractDate )
        )
    )

which takes the date found in my contracts table (sales[Date]) and determines whether that date fits between a date range in my employees table (Employees[Contract Start and Employees[Contract End]) and then will extract the corresponding city name (Employees[Office Name]).

The above works for situations where there only one contract for given sale date but returns empty results if multiple ranges are available or if the sale is made outside of an available date range for a given employee.

Any ideas?

Upvotes: 0

Views: 738

Answers (2)

W.B.
W.B.

Reputation: 5525

You need to create a calculated table, which will be used to select the location that you want. I don't exactly understand what you mean by "use the date range which has the least amount of days between them", so I will assume it's number of dates between start date and end date. If it's something else, you can always change the formula for calculating days.

This is just off the top of my head, I'm sure it can be optimised.

MyLocation = 
VAR CurrentContractDate =
    SELECTEDVALUE( Contracts[Date] )
RETURN
    IF (
        NOT ISBLANK ( CurrentContractDate ),
        VAR MyLocals =  //This gets all corresponding locations
            FILTER (
                Locals,
                Locals[Date Start] <= CurrentContractDate
                    && Locals[Date End] >= CurrentContractDate
            )
        VAR MyLocalsRanked = //This ranks the locations (in ascending order), based on 
            ADDCOLUMNS (     //number of days between start and end dates
                MyLocals,
                "Days", DATEDIFF ( [Date Start], [Date End], DAY ),
                "Rank", RANKX ( MyLocals, DATEDIFF ( [Date Start], [Date End], DAY ),, 1 )
            )
        VAR MyLocal =  //This selects the one with the highest rank
            TOPN ( 1, MyLocalsRanked, [Rank], 1 )
        RETURN
            SELECTCOLUMNS ( MyLocal, "Location", [Location] ) //This returns the name of the location
    )

As usual, when you look again, you see that there are some superfluous steps...

MyLocation = 
VAR CurrentContractDate =
    SELECTEDVALUE( Contracts[Date] )
RETURN
    IF (
        NOT ISBLANK ( CurrentContractDate ),
        VAR MyLocals =  //This gets all corresponding locations
            FILTER (
                Locals,
                Locals[Date Start] <= CurrentContractDate
                    && Locals[Date End] >= CurrentContractDate
            )

        VAR MyLocal =  //This selects the one with the lowest number of days
            TOPN ( 1, MyLocals, DATEDIFF ( [Date Start], [Date End], DAY ), 1 )
        RETURN
            SELECTCOLUMNS ( MyLocal, "Location", [Location] ) //This returns the name of the location
    )

EDIT

The modification to select contracts even when there is no matching date is fairly simple. The logic is basically the same - use ranking system to select best location. But first, you have to remove filtering based on dates and then you need to rank results and come up with a rank formula that will prefer matching locations and select non-matching as a last resort. For instance:

MyLocation =
VAR CurrentContractDate =
    SELECTEDVALUE ( Contracts[Date] )
RETURN
    IF (
        NOT ISBLANK ( CurrentContractDate ),
        VAR MyLocal =
            TOPN (
                1,
                Locals,
                IF (
                    CurrentContractDate <= Locals[Date End]
                        && CurrentContractDate >= Locals[Date End], //Check, whether there is matching date
                    DATEDIFF ( Locals[Date Start], Locals[Date End], DAY ), //If so, rank matching locations (you may want to employ a different formula)
                    MIN (  //If the location is not matching, calculate how close it is (from both start and end date)
                        ABS ( DATEDIFF ( CurrentContractDate, Locals[Date Start], DAY ) ),
                        ABS ( DATEDIFF ( CurrentContractDate, Locals[Date End], DAY ) )
                    ) + 1000000 //Add a discriminating factor in case there are matching rows that should be favoured over non-matching.
                ), 1
            )
        RETURN
            FIRSTNONBLANK(SELECTCOLUMNS ( MyLocal, "Location", [City] ) )//This returns the name of the location. In case of tie on the top spot, it returns the first value.
    )

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40244

How about this?

ContractLocation =
VAR CurrentContractDate = SELECTEDVALUE ( Contracts[Date] )
VAR ValidLocals =
    ADDCOLUMNS (
        FILTER (
            Locals,
            Locals[Date Start] <= CurrentContractDate
                && Locals[Date End] >= CurrentContractDate
        ),
        "@Length", Locals[Date End] - Locals[Date Start]
    )
VAR MinLength = MINX ( ValidLocals, [@Length] )
RETURN
    MAXX ( FILTER ( ValidLocals, [@Length] = MinLength ), [Location] )

This finds the minimal length contract and then returns the top location with that length.

Upvotes: 0

Related Questions