Connect Dates from different years by month

[Anonymous]

Hello

I have to do a monthly report for my organization, this report should have the values of the year until the present date and compare them with the homologous perríod, into tables and graphics, for the different departmentes in the organization. 

I have different excel files one to each department and each one with with two sheets, one to year N and another to year N-1, but I can't connect the dates by month in order to compare them, I have the dates in the row so I am able to filter them until the current month. Until now I have tables from year N connected and tables from year N-1 connected but I cannot connect diferent years. 

How can I do it? I already have a lot of measures donne so I cannot reorganized all the work done until now. 

In the image the column "Mes" have the complete date of the last day of the month. 

My table now apears like the one in the image with all the combinations of month's possible. But I want the values from: january - january; February- February; March - March, and so on. 

I hope the image helps to understand my problem.

Thank You

 

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Based on my knowledge and experience with Power BI, your dataset structure need to be re-arranged.

For example , for department1, there are tables, one to year N and another to year N-1.

You may create a calendar table with function “calendar”or “calendarauto”, then create  relationships between the calendar table and your tables based on date column.

Please look into the article to learn how to structure your data in power bi

Implementing Power BI Data Modeling with Avi Singh - YouTube

Tutorial: Shape and combine data in Power BI Desktop

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

Or you may provide a simple dataset example for me to analyze.

Best Regards

maggie

[Anonymous]

Thank you very much for your help. 

I am a begginer in Power Bi, and those videos that you show me, will gonna help me, for sure, to improve my Power Bi skills. 

Thanks. 

[Greg_Deckler]

Can you simply use an Append query to combine your two date tables?

[Anonymous]

I think it is not possible because the columns are not equal.