Forum Discussion
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])
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.
15 Replies
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 ?
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?
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?
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
