Forum Discussion

ManuelEspitia's avatar
ManuelEspitia
Helper II
5 years ago

Modeling excel data without time series

Hello everyone:

I need to do data modeling by viewing goals, indicators, and accumulated by quarter and year, from an excel file that doesn't have the data organized by date, it only has year tables and quarter columns, as seen in figure one (Full table in red, year in green, and quarters in yellow). How can I organize that information to display bar or line charts with values organized chronologically by year and by quarter?

I also need to make comparisons between budget and execution tables, which have similar structure, see image 2 (tables and year in red). These are the first two tables, scheduled and executed in 2020, but there are more than 2021, 2022 and 2023, with the same structure. How can I organize them so that I can compare values and visualize them in chronological order?

In the last image I leave you my data model, in which I simply created an indexed column to relate all the tables in the excel (relationships between tables in red).

Thanks for the help.

Consulta_EstructuraTiempo.pngConsulta_EstructuraTiempo2.pngModeloDatos.png

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Best approach would be to create surrogate dates from your existing fields. For example "2020"  can be translated to either 2020-01-01 or 2020-12-31 (whatever your preference).  Quarters can be pegged to the first day of the quarter, months to the first day of the month etc.

     

    Once that is in place you can use the regular time intelligence functions.

     

    What you show there is not a data model.  Data models do not have bidirectional search filters between all tables.   Keep in mind that Power BI works based on the concept of expanded tables.  Your approach will pretty much guarantee that Power BI will fail you.