Forum Discussion

Dudeman's avatar
Dudeman
Helper I
3 years ago
Solved

Simple(?) Lookup not working

Hi. Need a hand please. Here is a sample workbook: https://we.tl/t-T83HOaxQNj    I have 2 date tables and a sales data table. One of the date tables is special in that it aligns same day of the mo...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Dudeman 

    You can create the following measure:

    Measure = var _date = MAX('Invoice Date Table'[Date])
var _d=MAXX( FILTER(ALL('Alignment Table') ,'Alignment Table'[Date] = _date) , [SDayLM])
return
CALCULATE(SUM('Invoice Table'[Sales]) , 'Invoice Date Table'[Date] = _d)

     

    Best Regards!

    Yolo Zhu

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

bolfri's avatar
bolfri
Solution Sage
3 years ago

You need a DAX measure for this, but not LOOKUPVALUE.

 

Here is the measure for Sales:

Sales = SUM('Invoice Table'[Sales])
 
And here is a measure for Sales for Relative Dates that you have in Aligment Table:

Sales SDayLM =
VAR _RelativeDates = VALUES('Alignment Table'[SDayLM])
VAR _RelativeSales = CALCULATE([Sales],FILTER(ALL('Alignment Table'),'Alignment Table'[Date] in _RelativeDates))
RETURN IF(NOT(ISBLANK([Sales])),_RelativeSales)

 

Results:

 

Note that on the viz I've used dadte from AligmentTable, but you should consider disable automatic date hierarchy on your dates field and build a proper calendar table for your model.

 

Additionaly I didn't used anything from the Invoice DateTable, because I don't know the business meaning of that table or I can't see why do you need it.

 

Let me know if it's a solution for your case and take care!

Dudeman's avatar
Dudeman
Helper I
3 years ago

Hi Bolfri. Thanks so much for your help, I really appreciate it. Unfortunately I couldn't get your solution to work in my sample or master file, even after setting it up per your notes on the date tables. 

I was using Invoice DateTable as my Calendar table, which is why it was included.

 

Thanks for the tip on the proper date table - I'll consider that 🙂