question about Measure

[eitanl]

Hi, 

i have encounter a problem i will appreciate your thoughts on

made an example to simulate:

The DB is looks like that 

Date	Item	Current Status Code	Last Month Status Code	Chg Code	Val
31/05/2017	Pen	S	M	SM	1490
30/06/2017	Pen	A	D	AD	1580

i want to create a Table in power BI the Shows:

Date	30/06/2017
Chg Code	Val	Measure: Last Month Val
AD	1580	1490

i.e i am trying to create a measure that will show my the value of  Last Month  in the table of Chg Codes

the problem is i cant go back to DB with the Chg Code because it is not the same 

i have used a measure like Calculate(sum(Val),PREVIOUSMONTH(dates(date))  which works perfectly if the table has an item in the rows.  [ in guessi am looking for a formula that will take all items that have AD in their Chg Code for June 2017 and will create a virtual table for those items in the last month, and then sums their value without checking their Chg Code again)

any thoughts about how to perceive the result?

[ Just for an overview, i want to gather the past info of all items that has their code change between this month to the previous month from A to D)

hope i presented my problem clearly

thanks

[v-shex-msft]

Hi @eitanl,

As smoupre said, you can try to use other columns to group you records, then filter on current group and last date to find out the suitable records.

Calculate column:

LastMonth = LOOKUPVALUE([Val],[Item],[Item],[Date],DATE([Date].[Year],[Date].[MonthNo],1)-1)

Measure:

LastMonth 2 = 
var current_item=LASTNONBLANK(Sheet3[Item],[Item])
var current_date=MAX(Sheet3[Date])
return
LOOKUPVALUE(Sheet3[Val],Sheet3[Item],current_item,Sheet3[Date],DATE(YEAR(current_date),MONTH(current_date),1)-1)

Regards,

Xiaoxin Sheng

[eitanl]

Thanks,

i was trying that but i was afraid it takes a lot of computing resources and the report will work slow.

my DB is around 2-3 million rows and i need around 10 of these

do you recommend to use LOOKUPVALUE with big DB?

[v-shex-msft · Accepted solution]

Hi @eitanl,

>>my DB is around 2-3 million rows and i need around 10 of these

If you just want to get a summary records of each items, I'd like to suggest you use summarize function to group them and got the last val.

>>do you recommend to use LOOKUPVALUE with big DB?

As you mentioned , if you use measure to calculate through each records, it will spend the lots of system resources.

Regards,

Xiaoxin Sheng

[eitanl]

thanks

[Greg_Deckler]

Perhaps add an ALLEXCEPT([Item]) to your filter clause? In theory that would eliminate the Chg Code filter context in your table visualization?