CODEWITHSUNDEEP
powerbidax
Sviatoslav Piliaiev
Sviatoslav Piliaiev

Reputation: 19

Power BI DAX to find Data within specific period

The data is two tables 1 with Item name; Manager that was responsible for the item in a period; date from which they were on it (can be blank); date to which they were on it. And in Table 2 is the item name and when it was sold. What i need to find is a way to find in Power BI to add a column or measure that will show which manager was responsible for the item at that time. The image is from Excel just to simplify explanation.

Upvotes: 0

Views: 926

Answers (2)

Sviatoslav Piliaiev
Sviatoslav Piliaiev

Reputation: 19

Here is a possible solution. although it takes up a lot of PC memory to process if the amount of data is large.

test = CALCULATE(
        VALUES('table1'[Manager]),
        FILTER('table1',
        'table1'[DayFrom] <= EARLIER('table2'[When])
        &&
        'table1'[DateTo] >= EARLIER('table2'[When] ) ) )

If there are blanks, as there were in my case, in table1 DateTo, i made a new column and used it instead with DAX:

 Date to Blanks = IF('Table1'[DayTo] = BLANK(),
    TODAY(), 'table1'[DayTo])

Upvotes: 0

Kin Siang
Kin Siang

Reputation: 2699

Check my formula, it will return the result as expected, accept it if it help, thank you:)

Add Column = 
CALCULATE(
    MAX(Sheet1[Manager]),
        FILTER(ALL(Sheet1),Sheet1[item] = Sheet2[Item]),
            FILTER(ALL(Sheet1),Sheet1[Date from] <= EARLIER(Sheet2[When]) && Sheet1[Date to] >= Sheet2[When]))

enter image description here enter image description here

Upvotes: 2

Related Questions