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. 

     

MalS's avatar
MalS
Resolver III
9 years ago

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:

 

 

 

 

  • bhmiller89's avatar
    bhmiller89
    Helper V
    9 years ago

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

      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

       

       

      • bhmiller89's avatar
        bhmiller89
        Helper V
        9 years ago

        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?