Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Looking up values from a table with missing dates

I have a Table 1 that tracks increases and decreases in inventory, but only has line items for the days where the inventory changes:

 

Day

Item

Unit Total

1/1/18

Item A

10

1/3/18

Item A

5

1/5/18

Item A

20

1/1/18

Item B

10

1/5/18

Item B

15

 

And I have Table 2 with dates and items, and I need to indicate what the inventory is on each date for each item, like this:

 

Date

Item

Unit Total

1/1/18

Item A

10

1/1/18

Item B

10

1/2/18

Item A

10

1/2/18

Item B

10

1/3/18

Item A

5

1/3/18

Item B

10

1/4/18

Item A

5

1/4/18

Item B

10

1/5/18

Item A

20

1/5/18

Item B

15

 

I need to somehow lookup from the first table the value of Unit Total for the greatest date less than or equal to the given row and where the item is the same.

 

I can't figure out a simple way to do it. The direction I've taken has been to concatenate item and date on both tables ("Item-Date"), then use Lookup to pull in Unit Total for those dates where it exists:

 

Incomplete Totals = LOOKUPVALUE('Table 1'[Unit Total],'Table 1'[Item-Date],'Table 2'[Item-Date])

 

That gives me a column with values where they exist, and blanks for the rest.

 

Then I try to create a second column to fill in the blanks:

 

Daily Unit Total = CALCULATE(

    MAX('Table 2'[Incomplete Totals]),

    ALL('Table 2'),

    'Table 2'[Date] <= EARLIER('Table 2'[Date]),

    'Table 2'[Item] = EARLIER('Table 2'[Item])
)

 

Unfortunately, this formula takes the max Unit Total, rather than finding the Unit Total for the Max Date. So it works for Item B, which only increases, but doesn't decrease Item A when it goes from 10 to 5 on 1/3.

 

I'd love it if there's a better way to do the lookup so I can do it all in one step. Otherwise, I'd be interested to know how to structure my Calculate formula to get the Unit Total for just the max date less than the date in the current row, for the given item.

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @Anonymous,

 

Please try this formula:

Unit Total =
VAR Mostrecentday =
    CALCULATE (
        MAX ( Table1[Day] ),
        FILTER (
            Table1,
            Table1[Item] = EARLIER ( Table2[Item] )
                && Table1[Day] <= EARLIER ( Table2[Date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Table1[Unit Total] ),
        FILTER (
            Table1,
            Table1[Item] = EARLIER ( Table2[Item] )
                && Table1[Day] = Mostrecentday
        )
    )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yulgu-msft
Employee
Employee

Hi @Anonymous,

 

Please try this formula:

Unit Total =
VAR Mostrecentday =
    CALCULATE (
        MAX ( Table1[Day] ),
        FILTER (
            Table1,
            Table1[Item] = EARLIER ( Table2[Item] )
                && Table1[Day] <= EARLIER ( Table2[Date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Table1[Unit Total] ),
        FILTER (
            Table1,
            Table1[Item] = EARLIER ( Table2[Item] )
                && Table1[Day] = Mostrecentday
        )
    )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

 

This is the calculated field formula i used

 

=CALCULATE(MAX(inv_movement[Unit Total]),FILTER(inv_movement,inv_movement[Item]=EARLIER(inv[Item])&&inv_movement[Day]<=EARLIER(inv[Date])))

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur, this gets me slightly closer, giving me a value for each date without having to use two columns.

 

Unfortunately, the logic is still essentially faulty. It's saying: "Give me the max unit count for all dates on or before the current date for this current item." That gives me the wrong value if the unit count decreases. What I need is: "Give me the most recent unit count for this item on or before the current date"

Hi,

 

What happens when you replace MAX(inv_movement[Unit Total]) with LASTNONBLANK(inv_movement[Unit Total],1)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur, logicially it feels like LASTNONBLANK should work, but it had the same result as MAX.

Hi,

 

Please show the expected result.  Especially for the case then the inventory level falls.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.