LOOKUP return incorrect value for same ID

[Anonymous]

Hi guys, 

I'm walking in circles with this case, and I hope someone could help me.

I have two tables connected by One-To-Many relationship. 

Table 2 has the scenario below, where I have a column with the following formula:

EndThisMonth = IF(MONTH('DDA BaseVW_CostItem'[EndDate].[Date])=MONTH(TODAY()),"Yes", "No")

So far, so good.

In order to build a visual, I need to get the EndThisMonth status into Table1 based on CostItemID.

I tried a LOOKUP but I've got the error 'A table of multiple values was supplied where a single value was expected.'

Then this formula seemed to work:

PackageEndThisMonth =

CALCULATE(FIRSTNONBLANK('DDA BaseVW_CostItem'[EndThisMonth],1), FILTER(ALL('DDA BaseVW_CostItem'),VW_PlacementMonthlyBilling[CostItemId]='DDA BaseVW_CostItem'[CostItemId]))

But when I put the data from both tables side by side, I noticed the calculation above returns a different value for the same ID.

Does anyone know how I could fix this? I've also tried the formula below, but the same issue.

PackageEndThisMonth =

MAXX(

FILTER(

'DDA BaseVW_CostItem',

'DDA BaseVW_CostItem'[CostItemId]=EARLIER(VW_PlacementMonthlyBilling[CostItemId])

),

'DDA BaseVW_CostItem'[EndThisMonth]

)

[Anonymous]

Hi @Anonymous  and @amitchandak 

I updated the sample date. I'm still having the same issue 😕

If someone could give me a light here

https://drive.google.com/file/d/1ItKvrbbFpq3mqrbbwC0ez-J_vsLFr60A/view?usp=sharing

Thank you

[Anonymous]

@Anonymous 

Now, there is no July date in the table, we cannot see the issue with your sample. Maybe update your sample table to include newest month.

Paul Zheng _ Community Support Team

[amitchandak]

@Anonymous , This one seems correct

ackageEndThisMonth =

MAXX(

FILTER(

'DDA BaseVW_CostItem',

'DDA BaseVW_CostItem'[CostItemId]=EARLIER(VW_PlacementMonthlyBilling[CostItemId])

),

'DDA BaseVW_CostItem'[EndThisMonth]

)

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak 

I uploaded a sample here. You'll see the PackageEndThisMonth returns YES for activities that ended last month

https://drive.google.com/file/d/1Uv8l6RVM6Kc36v16OMW9ObLtetki2LY4/view