Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!
@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!
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.
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.
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.
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
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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
75 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |