Simple(?) Lookup not working

[Dudeman]

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 month to last month within the same week (ex. 1st Monday of this month to 1st Monday of last month etc). 

In a single matrix I want to show this month's dates, the sales amount for this month, and the sales amount for the corresponding same day last month based on my alignment calendar. 

I think I should be using LOOKUPVALUE but I can't get PBI to "take" my third argument - it doesn't want to look up the Invoice date in my Alignment date table, specifically SDayLM (same day last month) field.

In the example below I want to create a measure to put next to MTD Sales called SDLM Sales, and the first number should be 256,806, the second should be 1,628,932, and so on.

Attached is a workbook as well. Please note, in my real workbook The Invoice Table and Invoice Date Table cannot be changed whatsoever - they're part of a Live model. I can only change the relationship etc for the Alignment Table.

Thanks!

[Anonymous · Accepted solution]

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.

[Dudeman]

@Anonymous Thank you!! This worked!

One thing still not quite working is the total for the new measure. 

I would expect to see a number in the red boxes below:

How can I get the total to show? I don't necessarily need it in the top table, but I'd like to at least see it in a single total line like the bottom table.

I tried to create a new measure using TOTALMTD with both the Invoice Date or the SDayLM fields referenced, but couldn't get that to work. Anything I created came back with the current January MTD total instead of the Same Days Last Month's days total. 

Thank you again for your help.

[bolfri]

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]

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 🙂