Getting a date from one table and into another

[ajaydavidluke]

Good afternoon all,

I want to import a date 'Next Supply Base Prod'[Emb Estimated D] into 'Next Supply Date' table.

I would like then to create a dax based on:

if [Expected Delivery Date] is blank then show me [Emb Estimated Delivery Date] else [Expected Delivery Date].

Am I asking too much in this instance??

Many thanks in advance 🙂

[Anonymous]

Hi,

I have an approach that might work well, but it depends on the following question. Does any Prod/Colour have a single 'Emb Estimated Delivery Date' or 'Expected Delivery Date' associated? If that is the case, you might try this formula. 

Expected/Estimated Delivery Date =
VAR prod_colour = [Prod/Colour]
RETURN
    IF (
        [Expected Delivery Date] = BLANK (),
        CALCULATE (
            SELECTEDVALUE ( 'Next Supply Base Prod'[Emb Estimated Delivery Date] ),
            'Next Supply Base Prod'[Prod/Colour] = prod_colour
        ),
        [Expected Delivery Date]
    )

Regards,

Marc

[ajaydavidluke]

Hi Marc,  

Thank you for your solution:  You can view the results below, I'm definitely closer but I should be expecting to see 'Next supply date' in the 'Expected/Estimated Delivery Date' column where 'Expected Delivery Date' is blank but I'm seeing the opposite to that?

[Anonymous]

Hi,

In which table are you working? I do not see 'Next Supply Date' column in any table. Maybe i have not understood well the problem.

Thanks,

Marc

[ajaydavidluke]

Hi Marc,

Apologies I had renamed the column, i've reveiwed this and have posted the results below, the approach you have suggested has indeed returned a value but not what I was expecting.........The theory should be If 'Expected Delivery Date' is blank then show 'Emb Estimated Delivery Date' else show me the 'Expected Delivery Date'

The suggested approach is showing the same value as originally ?

In answer to one of your earlier questions Does any Prod/Colour have a single 'Emb Estimated Delivery Date' or 'Expected Delivery Date' associated? Whilst 'Expected Delivery Date' is blank, there are instances where both 'Expected Delivery Date' & 'Emb Estimated Delivery Date' have a value.

[Anonymous]

Hi,

As i can see in the image, the solution is less complex than i thought. Try this DAX, i guess it will work.

Expected/Estimated Delivery Date =
VAR prod_colour = [Prod/Colour]
RETURN
    IF (
        [Expected Delivery Date] = BLANK (),
        [Emb Estimated Delivery Date],
        [Expected Delivery Date]
    )

Regards,

Marc

[ajaydavidluke]

Hi Marc,

I applied the formula but found it didnt like the Emb Estimated Date:

It doesnt give me the option to select from a different table?

So I have used SELECTEDVALUE to access the column from another table and have the below:

However the DAX does not return a value:

I really appriciate your help, my DAX techniques and understanding needs developing as I'm pretty new to Power BI.

[Anonymous]

Hi,

In order to get the correct solution, i need to understand a bit more of the model. Does 'Prod/Colour' column has unique values? I mean, this column does not have any repeated value in 'Next Supply Date' or 'Next Supply Base Prod'.

Thanks,

Marc

[ajaydavidluke]

Hi Marc,

In answer to your question, yes, there may be a value in 'Next Supply Date' which is different to 'Next Supply Base Prod' but in most cases 'Next Supply Date' is blank.  What I am trying to acheive is that once a product has been Emb, it changes to a new product code, therefore when date is blank, I want it to look for a delivery date based on base product not Emb product.

Where there is an exisiting value in 'Expected Delivery Date' use that exisiting value, where value is blank, pull in date from 'Emb Estimated Delivery Date'

I hope this helps 🙂 

[Anonymous]

Hi,

Let's do a middle calculation that will help us in this situation. Create a column 'Emb Estimated Delivery Date' in 'Next Supply Date' table. Try with this formula:

Emb Estimated Delivery Date =
VAR prod_colour = [Prod/Colour]
RETURN
 CALCULATE (
            SELECTEDVALUE ( 'Next Supply Base Prod'[Emb Estimated Delivery Date] ),
            'Next Supply Base Prod'[Prod/Colour] = prod_colour
        )

What will this formula do? For any value of Prod/Colour in 'Next Supply Date' it will check if it has a value of 'Emb Estimated Delevery Date' in 'Next Supply Base Prod' and will give it. Confirm if it is working hell.

Regards!

Marc

[ajaydavidluke]

Hi Marc,

I very much appriciate your help,

I have created new Column:

I have entered the DAX:

There are lots of blanks in 'Emb Estimated Delivery Date NEW Column' when I would like to be seeing those blanks filled in with the date from 

The formula is half working, in that it is detecting a value but it doesnt seem to pull in any date where the value is blank.

Many thanks in advance for your time 🙂