Always show this month allocation keys

[Anonymous]

Hi All, 

I would like to create a measure that multiplies the number of FTEs (table1) by an allocation key(table2). The trick here is that the allocation key should ALWAYS be this month's. i.e 0.2. For example if we are looking at Nov21, it should still be multiplied by 0.2. however, if we move into next month, then next month's allocation key will be taken but set for the rest of the year. 

[Kucrapok]

Hello,

Can you give us an idea of what table you have in your model and what columns you have in these table?

[Anonymous]

Hi, 

like in the above picture, i have No of Full Time Employees by date. I also have another time called allocation keys (also in the picture) by date and lastly of course i have a date table. im trying to create a measure by doing sum(No of FTEs) * calculation(sum(allocation keys %), month(today())

[v-shex-msft · Accepted solution]

Hi @Anonymous,

Did you mean to dynamic calculate results based on the current system DateTime value and lookup the rate from another table?

If that is the case, you can create a value in your formula with today function to get the current system date, then you can use it in lookupvalue function to search rate from another table for the following calculations.

Introducing LOOKUPVALUE - SQLBI
Regards,

Xiaoxin Sheng