cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MalS Member
Member

Re: Time Intelligence

Your data model is quite complex, so I haven't attempted to trace the calculations through...but if you filter your sales measures on "closed" opportunities, then that should work ok.

 

There is some mismatch between your date field in the DimDate table and CloseDate field in the "dpmgr..." table though - even though there is an active relationship between them. And since your sales measures (Sales$MTD, Sales$YTD, etc.) use the DimDate[Date] field, that may be causing some of the issues.

 

Perhaps try parsing out the date from the date/time CloseDate field and using just the date in your relationship? 

bhmiller89 Member
Member

Re: Time Intelligence

Could you possibly elaborate on the mismatch in dates?

Sean Super Contributor
Super Contributor

Re: Time Intelligence

@bhmiller89

 

Look at my response here and the pictures...

http://community.powerbi.com/t5/Desktop/Count-by-Category/m-p/83368#U83368

The example is different but you'll see what happens when you use different dates!

 

hope this helps.

Good Luck!Smiley Happy

Highlighted
bhmiller89 Member
Member

Re: Time Intelligence

@Sean ah I see. I made a table including the datekey (dimdate) column and the close date column and one is completely blank where the other isn't.

 

I'm just not getting it at all, can't seem to figure this stuff out. Thanks for you help, I'm just going to throw in the towel!

Sean Super Contributor
Super Contributor

Re: Time Intelligence

@bhmiller89

Okay I understand!

If you decide to revist this at a later time check out this post by @MattAllington

http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/

He uses an example with Ship Date and Order Date

Good Luck! Smiley Happy

MalS Member
Member

Re: Time Intelligence

You are probably closer than you realize, so I will just throw this out there. 

 

Thinking about the dates as numbers it might help... 

 

If you have a value of '1 January 2017' in the DimDate table, it is represented as the number 42736. But if you have a value of '1 January 2017, 13:46' in the CloseDate field, it is represented as 42736.57 (you can check this in Excel by entering these values, then formatting the cell as a number).

 

If you have a relationship between these two fields, Power BI thinks that the values above do not match. That makes sense to a computer, because they are different numbers. That's why one column is blank while the other isn't when you add them to a table. But you want Power BI to assume that everything that happened on 1 January 2017 should be linked (regardless of the time it happened on that day). 

 

So you can try this:

 

1. click on edit queries

2. expand and click on the CloseDate column

3. click the Add column tab

4. Click the Date button and select 'Date Only'

 

This will add a new column that contains only dates (no times). Use that new date-only field in your measures, etc. and see if that helps. 

 

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 162 members 1,519 guests
Please welcome our newest community members: