Subtracting days form date

[cocoloco79]

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?

[cocoloco79 · Accepted solution]

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 

[SteveHailey · Accepted solution]

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 · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[cocoloco79 · Accepted solution]

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 

[SteveHailey · Accepted solution]

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

[SteveHailey]

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