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?

      • MalS's avatar
        MalS
        Resolver III
        9 years ago

        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?