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.

Anonymous's avatar
Anonymous
Not applicable
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.

Dudeman's avatar
Dudeman
Helper I
3 years ago

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.