Asociar el valor de una fecha completa a todo el mes

[MacarenaGB]

Hola a todos!

Tengo un problema que seguro que es muy facil de usar.

Para hacer una medida tengo que utilizar un valor que está relacionado con un día concreto

31/12/2020  --- 3000

30/1/2021 ---- 4000

28/2/2021 ---- 3500

Necesito que ese valor se asocie a todos los días de cada mes.
¿Cómo puedo hacerlo?

Gracias!

[MacarenaGB]

@veenashenolikar 
The data come froma google sheet

And in other tables I have reported by week (with an initial date of the week and the end of the week) the time spent in each project

What I need is to calculate the real cost based on the relationship of the email and Employee name, but, as the effort tracking can be asociated with 2 dates, and the employee cost os the previous table is given with the billing date (third party resources), I can't asociated that cost with the whole month of the billing date to calculate it. 

Am I more clear now? 

TIA!

[v-stephen-msft]

Hi @MacarenaGB ,

I modified the sample data. 

Then I create a calculated column still with LOOKUPVALUE function.(Increased the distinction between different employees.)

Column = LOOKUPVALUE('Table'[Value],'Table'[Date].[MonthNo],[Date].[MonthNo],'Table'[Employee],[Employee])

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MacarenaGB]

Hi @v-stephen-msft 

Sorry but I think that this solution is very convoluted for the problem.
First, I need to associate the expense to all days of the month and if I have to repit the calenda per employee it is going to be so many complex.

Isn't there any dax formula I can create like instead of using additional tables?

Thanks anyway for your help for sure.

[v-stephen-msft]

Hi @MacarenaGB ,

If you don't want to create additional tables, please use the LOOKUPVALUE function directly in the original table.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MacarenaGB]

 @v-stephen-msft 

I am trying your solution anyway but, I have issues... I have more than one value (Billing Date) that can be repited (because there are many employees), so I can not use the LOOKUPVALUE function.

Any other option?  @veenashenolikar  @v-stephen-msft 😁

[v-stephen-msft]

Hi @MacarenaGB ,

Could you please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business?

Remember to hide sensitive information.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MacarenaGB]

Hi! @v-stephen-msft I wouldn't like to create a column calculated in the Calendar Table. 

Is there any other option? I don't like so much mix the calendat with intermediate data

TIA

[v-stephen-msft]

Hi @MacarenaGB ,

You could try to use LOOKUPVALUE function like this:

1.Sample data

DateValue

12/31/2020	3000
1/30/2020	4000
2/28/2020	3500

2.Create a calculated table

Table 2 = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
    "Value", LOOKUPVALUE ( 'Table'[Value], 'Table'[Date].[MonthNo], MONTH ( [Date] ) )
)

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MacarenaGB]

Hi @veenashenolikar !

The values came from a table. The date is the billing date of a human resource cost (third party) and I want to make calculations with the cost and the number of days he has worked that month (another table) which are reported by week. That's the reason why I want to know the cost per day each month.

BR

[veenashenolikar]

Hi @MacarenaGB 

The values you want associated with the day.. are they coming from some column as value or random numbers will work?

Regards,

Veena Shenolikar