Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
9 years ago
Solved

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 appea...
Need Help
  • MalS's avatar
    MalS
    9 years ago

    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. 

     

JasonP's avatar
JasonP
Frequent Visitor
9 years ago

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's avatar
Vvelarde
Community Champion
9 years ago

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