Calculating prior month values

[kiril]

We have a monthly data export which creates plain csv files of a Project table. The monthly data exports contain the whole table. Thus, the base data we have is:

projects_2022_01.csv

projects_2022_02.csv

projects_2022_03.csv

...

A project has a state that can be scheduled, active, finished, overdue. I want to create a table which shows the current state of the projects and a comparison with prior month grouped by state:

	Current (data from projects_2022_03.csv)	Prior Month (data from projects_2022_02.csv)
Scheduled	5	6
Active	2	1
Overdue	1	0
Finished	10	9

I can easily load the data into a single table in Power BI, but I wonder if that's the best data model?

[v-yalanwu-msft · Accepted solution]

Hi, @kiril ;

You could combine this excel files in power query .

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-combine-binaries

1.Put all your workbooks in a folder, then click Get Data->Folder-> Edit.

2.After step 1, click context column then click Combine Files like below:

3.Choose which type of sheet you want to combine to a single table, here I choose Sheet 1, it means sheet1 in all the workbooks will be combined to a single table:

4.The result is like below:

5.In desktop, we could use matrix.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and the CSV files.

[kiril]

There is nothing really amazing in the pbi file, except for the loaded csv files. The files look like the following listing.

projects_2022_01.csv:

Project name	Export date	State
Project A	2022-01-31	Active
Project B	2022-01-31	Overdue

projects_2022_02.csv:

Project name	Export date	State
Project A	2022-02-28	Active
Project B	2022-02-28	Finished
Project C	2022-02-28	Scheduled

After importing those files to Power BI, there is one table in Power BI:

Project name	Export date	State
Project A	2022-01-31	Active
Project B	2022-01-31	Overdue
Project A	2022-02-28	Active
Project B	2022-02-28	Finished
Project C	2022-02-28	Scheduled

From this combined table I am trying to calculate current month values and prior month values:

	Current (2022-02-28)	Prior month (2022-01-31)
Scheduled	1
Active	1	1
Overdue		1
Finished	1

[Ashish_Mathur]

Hi,

Create a Calendar Table and write calculated column formulas to extract Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the Export Date column to the Date column of the Calendar Table.  To your matrix visual, drag Year and Month name from the Calendar Table to the Column well and State to the row well.  Write these measures:

Count = countrows(Data)

Hope this helps.

[davehus]

Hi @kiril, Do you have a date column in the csv file and a date table in your model?

[kiril]

Yes, there is a date column present