Calendar relationship issues

[Voose]

Hi All,

I'm trying to pull a basic data through my calendar so I can see values over time.

I have a Calendar table that calculates by taking the min and max value of my date field. 

My opportunity date field is comprised of a formula that states if(isblank(historicaldate),createddate,historicaldate).

Now my Historical dates all map properly through the relationship of the calendar. However any of the dates that are from the createddate column do not use the relationship and act as if there is no relationship at all between the opportunity date field and the calendar date field.

all 3 columns (historical date, created date, date (calendar date)) all have the exact same formatting...

Any ideas guys?

Thanks

Voose

[v-yuezhe-msft · Accepted solution]

@Voose,

Please review my reply in this similar thread, the above issue could occur when you change the format of the opportunity Date field from DateTime to Date, in this scenario, not all the datetime values in your opportunity table match the date values in your calendar table.

Regards,
Lydia Zhang

[Voose]

Thank you Linda!

This was my issue and its fixed now 🙂

[vanessafvg]

is your relationship of your calendar date mapped to opportunitydate?

does the calendar date include all dates from opportunity date

[Voose]

YEp it is mapped to that date, and the calendar object is made up of Min(opportunity date) and Max(opportunity Date)

[Voose]

IF I change the parameters of the If statement to = historical date

It pulls through the date fine using the relationship as expected,

If I change the parameters of the column to = createddate 

Nothing pulls through

[vanessafvg]

that is strange, if you created the opportunity date in power query rather than dax in a calculated column would that make a difference maybe?

[Voose]

How would I create it in Power Query? -> Edit Queries then? - I can't merge it with my Calendar tables as obviously thats  a calculated table 😞

[vanessafvg]

what i would do is first try and figure out why the date is not working in the one you have created i.e create 2 columns opporunitydate 1 and opportunitydate 2 one without createddate and one with and see where the differences are and see if you can correct it in the if statement.

it is hard to figure out what the issue is without seeing the structure of your tables, if you could post a screen shot of your relationships that might help.

the power query option is for me a better option because its shaping the data how you want it,  i.e create calculated column exactly like you have in dax in the table you currently have it, unless the table its created in now is the calculated table that is calculated in dax not power query ?  i.e in power query add column / create custom column and then have the same if statement i.e 

if historical date = "" then createddate

else historicaldate

i am not sure what difference it would make but it might.

[Voose]

Going to try and make this simpler:

Hopefully these diagrams should be really clear as to whats happening with my data 😞

Let me know

Thanks

Voose

[vanessafvg]

@Voose nothing sticks out as obvious to me

but maybe it would  be best to create the min and max of the calendar date based on the opportunity created date (im assuming that what your if column is made up of) because based on what you said previously that should be the combo of the 2, rather than creating it on min of historic and the max of opportunity snapshot.

[Voose]

Done & Still no solution 😞

[vanessafvg]

If you want to post your pbix here i can have a look at it but i think you probably have to dig into your date field to find the issue, something isnt tallying up, personally i would push this sort of tranformation into power query.