Tracking Month on Month Project Status

[GJUDGE]

Hi, 

I am a PowerBI novice and trying to recreate a table which I have in excel that shows the status of a group of projects from one month to the next. I've tried to summarise this below.

I have tried appending the two monthly tables but I can't work out how to group each months data separately in a single Power BI output. At the moment, my output table is capturing the project's latest status across both months, when this may have changed. 

Any help would be appreciated (I'd also appreciate really straightforward explanations!)

January
Project	Status	Output
A	Feasibility	40
B	Planning	100
C	Feasibility	50
D	Pre-Planning	75
February
Project	Status	Output
A	Pre-Planning	50
B	Construction	100
C	Feasibility	50
D	Planning	75
Desired Output
Project Status	Output in Feb	Output in Jan	Monthly Variance
Feasibility	50	90	-40
Pre-Planning	50	75	-25
Planning	75	100	-25
Construction	100	-	100
TOTAL	275	265	10

[Anonymous · Accepted solution]

Hi @GJUDGE ,

You could specify the column to group and the desired output in Power Query Editor.

Then in ‘Home’ tab, Merges Queries>>Merge Queries as new.

Finally, create a column performing calculations on the Power BI Desktop.

desired output = Merge1[Output] - Merge1[January.Count]

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Polly

[amitchandak]

@GJUDGE , if they are two tables, create two common tables, project and status and join with both tables and analyze the data together , you can use measure from both table and use them in visual against common dimension

refer if needed

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19