Forum Discussion

RaulPBI's avatar
RaulPBI
Helper II
9 years ago
Solved

PROBLEMS WITH TIME INTELLIGENCE

Hello everybody.

 

I have a problem with a model. It contains only three tables, one of them is date, and the rest are fact tables, costs by date and shipments by date. The thing is that I am not able to "draw" in Power BI total shipments by date using date table, it does not work. Any idea?

Spoiler
 

 

Need Help
  • RaulPBI's avatar
    RaulPBI
    9 years ago

    Hi,

     

    I have solved it by this way:

     

    I have created another column in fact table which is date too, like this:

     

    Calculated date = albaran_salida_fechas[create_date].[Date]

     

    then I changed the relationship between these two tables, so "date" in calendario table is connected by"calculated date" with fact table. That´s perfect for me.

     

    Thanks for your time

6 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Hey,

     

    can you please be a little more specific what you mean by "It does not work"

     

    Check the following

    • are the tables related
    • ideally both columsn should be of datatype "date" sometimes it might happen, that the column from the fact table (cost or shipment) contain time information, whereas the date column of the calendar table might not match 12:00AM

     

    Cheers

     

    • RaulPBI's avatar
      RaulPBI
      Helper II
      9 years ago

      1. tables are related for sure

      2. both columns have the same format

       

      What I mean is that if I want to calculate shipments by date in a column chart for example, the chart does not show what I need. Instead of that show all data in "blank" date, not in each real date. 

      • TomMartens's avatar
        TomMartens
        Super User
        9 years ago

        Hey,

         

        my assumption:

        the x-axis is picked from your table calendario.

         

        Both tables are joined "Calendario-Date" <- "albaran...create_date" (the fact table).

         

        The fact that all your data is shown on the element "(blank)", that for sure doesn't exists in the table Calendario, indicates that there is no match between the values of the joined columns.

         

        This may happen if your Date column from the Calendario table is of datatype datetime "2017-08-10 12:00:00" and your column create_date is also a datetime column, but the values do not have a time portion like so: "2017-08-10 00:00:00"

         

        This means, there are no matching values in both columns. For this reason I really recommend that you define the same datatye "date", this is different than the format used to present the values from the columns:

         

         

         

        Hope this helps