Forum Discussion
Calculating prior month values
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?
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.
6 Replies
Yes, there is a date column present
Hi,
Share the download link of the PBI file and the CSV files.
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 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.
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.
