Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bhmiller89
Helper V
Helper V

Time Intelligence

Having an issue with my time intelligence calculations.  I calculated "SalesYTD," "SalesMTD," and "LastMonthSales."

 

For some reason it's including December 2016 in the YTD sales and doesn't appear to be accurately calculating "LastMonthSales" 

 

I used:

 

LastMonthSales$ = CALCULATE([TotalServices$], PARALLELPERIOD('Calendar'[Date], -1, MONTH))

 

MTDSales$ = TOTALMTD([TotalServices$], 'Calendar'[Date])

 

YTDSales$ = TOTALYTD([TotalServices$], 'Calendar'[Date])

Capture.JPG

 

 

 

 

1 ACCEPTED SOLUTION

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

15 REPLIES 15
MalS
Resolver III
Resolver III

I don't see exactly the same table in your sample .pbix file, and I don't see measures with exactly the same names.

But, there is a similar table without a date column. As suggested by @Baskar and @parry2k, there should be a date column in there.

 

If I add one the results look ok:

 

Table.png

 

 

 

@MalS except that LastMonth Sales should be for December 2016, why would it be including December 2016 in YTD Sales for 2017? Is there a step I'm missing?

Sean
Community Champion
Community Champion

@bhmiller89

Can you post a picture of the Visual that includes the December 2016 data in the 2017 YTD?

 

Please include a Date field in this Visual so we can see it says the Sales$YTD amount is specifically for 2017

 

YTD Picture.png

 

I think the trouble I'm having may be due to how my company's sales data works.

 

Each opportunity has a "Close Date" but it's projected or actual. So an opportunity can have a future close date with a status indicating it is "Pending" or it can have a past close date with a status of "closed" or "pending."

 

Does anyone think this could be messing things up?

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? 

Could you possibly elaborate on the mismatch in dates?

Sean
Community Champion
Community Champion

@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

@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!

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. 

 

Sean
Community Champion
Community Champion

@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

JasonP
Frequent Visitor

Not to hijack this post but I was having the same problem. with my MTD and YTD calculations using the same formula.

 

Initially, I was using my closed sale date as date reference however if a sales associate didn't have a sale this month or year, it would display the last period data in which they did. 

 

I then made a Date Table using the following formula

 

DateTable = ADDCOLUMNS(CALENDAR(DATE(2012,1,1), DATE(2020,12,31)),"DateAsInteger", FORMAT([date],"YYYYMMDD"),"Year",YEAR([Date]),"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

 

Then I discovered that my max date in my date table was set 2020 (as I naively thought I wouldn't have to update it anytime soon) so I was showing blanks in my data as it was trying to use 2020 and december 2020 for YTD and MTD (I think). 

 

Once I set my date table to this year and month, everything worked fine but that brings up my main question......

 

How to I set my max date for my calendar to use the max date of closing date so I never have to update the dates in the datetable

Vvelarde
Community Champion
Community Champion

@JasonP

 

NewCalendar=Calendar(Date(Year(Min(Table1[Date])),1,1),Date(Year(Max(table1[Date])),12,31))

 

or 

 

NewCalendar=Calendar(Date(Year(Min(Table1[Date])),1,1),Max(Table1[ClosingDate]))




Lima - Peru
parry2k
Super User
Super User

I believe you have date column in the table, as @Baskar mentioned, seems like there is no full image of your table.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Baskar
Resident Rockstar
Resident Rockstar

Cool,

 

Can u please share some sample data and Share the whole table image in Power BI. Ex: First column missing in your post right ?

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.