Forum Discussion
Helper IIISubtracting days form date
Hi there
I have two tables for witch I have created a relationship that looks at crop.
One table holds a harvest date column and the other table holds number of days to grow plants.
I like to subtract the number of days from the harvest date to calculate the planting date.
Can anyone help me with this?
Hi Steve, thank you but this didnt work. I get an error stating that the column gow time doesnt exist or there is a relationship missing. I did create the relationship, but it is not beeing picket up in the function.
I have attached the pbix, I hope you don't mind thinking a look.
https://drive.google.com/file/d/1uZFHig9Jnv_6R1n1EONixlZ8UNBZbciF/view?usp=sharing
Hey cocoloco79 . The relationship you had was many-to-many which is problematic. I updated your .pbix and it is linked here.
This was the relationship in your file:
I use this code to create a bridge table:
Grow Times = ALL( BI_Planting_Master[CropName], BI_Planting_Master[CropGrowTime])And then updated the relationships:
And then updated the fields in the table visual:
Someone else may have a better way to do this.. but this is they way I know. Hope that works for you!
-Steve
4 Replies
SteveHaileySolution Specialist
Hello,
One option is to create a calculated column in your Harvest Date table to pull in the grow time from the related table:
Grow Time = RELATED('Grow Times'[CropGrow Time] )Then add a calculated column to subtract the days:
Planting Date = 'Harvest Date'[Date] - 'Harvest Date'[Grow Time]Then create a table visual to match your example:
Here's a link to an example .pbix file.
-Steve
cocoloco79Hi Steve, thank you but this didnt work. I get an error stating that the column gow time doesnt exist or there is a relationship missing. I did create the relationship, but it is not beeing picket up in the function.
I have attached the pbix, I hope you don't mind thinking a look.
https://drive.google.com/file/d/1uZFHig9Jnv_6R1n1EONixlZ8UNBZbciF/view?usp=sharing
- SteveHailey
Hey cocoloco79 . The relationship you had was many-to-many which is problematic. I updated your .pbix and it is linked here.
This was the relationship in your file:
I use this code to create a bridge table:
Grow Times = ALL( BI_Planting_Master[CropName], BI_Planting_Master[CropGrowTime])And then updated the relationships:
And then updated the fields in the table visual:
Someone else may have a better way to do this.. but this is they way I know. Hope that works for you!
-Steve
- Ashish_Mathur
Super User