Forum Discussion
Calculating prior month values
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.
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.