Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
MacarenaGB
Helper II
Helper II

Asociar el valor de una fecha completa a todo el mes

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!

10 REPLIES 10
MacarenaGB
Helper II
Helper II

@veenashenolikar 
The data come froma google sheet

MacarenaGB_0-1610439570849.png

 

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

MacarenaGB_1-1610439743880.png

 

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!

 

Hi @MacarenaGB ,

 

I modified the sample data. 

10.png

 

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])

 

11.png

 

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.

 

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.

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
Helper II
Helper II

 @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 😁

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
Helper II
Helper II

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
Community Support
Community Support

Hi @MacarenaGB ,

 

You could try to use LOOKUPVALUE function like this:

1.Sample data

DateValue

12/31/20203000
1/30/20204000
2/28/20203500

 

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] ) )
)

27.png

 

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
Helper II
Helper II

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.