Solved: Time Intelligence - Page 2

bhmiller89 · ‎01-24-2017

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])

MalS · ‎01-30-2017

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