Forum Discussion
Helper IAnalyze Monthly Changes in a Table
Hi everyone,
I need to analyze the changes that occur in class data by comparing the current month with the previous month. To illustrate, I have an example covering the last three months, showing the most important columns.
The expected result is a table highlighting the rows that have changed (or perhaps displaying them as pairs: "previous-current"—I’m not sure which approach would be better).
Could you guide me on the best way to approach this? I’m not sure how to start while keeping it as simple as possible. How can I do the comparison?
The data comes from an Excel file containing the last 15 months, and I’m also unsure how to handle the transition between years.
Any advice would be greatly appreciated!
Hi Charcho ,
Apologies for the delay, and thank you for your patience.
I've made some updates to the report based on your feedback. Specifically, I created a new NextMonthKey in Power Query within the Previous Month Data table. Then, I used this to merge with the main table using Student Number and the DateKey (from the current month) and NextMonthKey (from the previous month table).
Please check the attached .pbix file.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
14 Replies
- lbendlin
Super User
CharchoThank you lbendlin , but the topic seems a bit complex for me to adapt to my example. I have attached the information in this link, in case you can take a look at it. https://www.dropbox.com/scl/fo/9qzy7t1nti2hgt0b2bsnr/AOXdgYX-n0wpPpMvDubjmVk?rlkey=z9g1t839bvk569oyxi550pqdr&st=5jzhc5r4&dl=0
- lbendlin
Please provide better sample data. Your tables are identical, there are no differences.
You may want to consider appending these table with their true dates (no need for year and month columns, that can be done with formatting).
v-prasareCommunity Support
Hi Charcho,
Thanks for reaching out to Fabric community support
try below steps to Track Changes in Power BI:
Create a "YearMonth" column in the main table (ClassData):
YearMonth = FORMAT([Year] * 100 + [Month], "0000-00")
Duplicate the table (ClassData) and create a new table (Previous Month Data).
Add "PreviousYearMonth" column in Previous Month Data:PreviousYearMonth = FORMAT(
IF([Month] = 1, ([Year] - 1) * 100 + 12, [Year] * 100 + [Month] - 1),
"0000-00"
)Create a new calculated table (comparetwotables) using NATURALINNERJOIN:
comparetwotables =
NATURALINNERJOIN(
SELECTCOLUMNS(
'ClassData',
"YearMonth", 'ClassData'[YearMonth],
"Student Number", 'ClassData'[Student Number],
"Current Final Register", 'ClassData'[Final register],
"Current Time Inv", 'ClassData'[Time Inv],
"Current Music Team", 'ClassData'[Music Team],
"Current Instrument", 'ClassData'[Instrument]
),
SELECTCOLUMNS(
'Previous Month Data',
"YearMonth", 'Previous Month Data'[PreviousYearMonth],
"Student Number", 'Previous Month Data'[Student Number],
"Previous Final Register", 'Previous Month Data'[Final register],
"Previous Time Inv", 'Previous Month Data'[Time Inv],
"Previous Music Team", 'Previous Month Data'[Music Team],
"Previous Instrument", 'Previous Month Data'[Instrument]
)
)Add a new calculated column (didchange) in CompareData:
didchange =
IF(
'comparetwotables'[Current Final Register] <> 'comparetwotables'[Previous Final Register] ||
'comparetwotables'[Current Time Inv] <> 'comparetwotables'[Previous Time Inv] ||
'comparetwotables'[Current Music Team] <> 'comparetwotables'[Previous Music Team] ||
'comparetwotables'[Current Instrument] <> 'comparetwotables'[Previous Instrument],
"Changed",
"No Change"
)Apply conditional formatting in Power BI to highlight rows where didchange = "Changed".
Create a slicer to filter changes by specific months.
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
Hello Charcho,
Please use the below mentioned DAX formula to create a new Calculated table.comparetwotables_alt =NATURALINNERJOIN(SELECTCOLUMNS('ClassData',"YearMonth", FORMAT('ClassData'[YearMonth], "YYYYMM"),"Student Number", FORMAT('ClassData'[Student Number], "General Number"),"Current Final Register", FORMAT('ClassData'[Final register], "General Number"),"Current Time Inv", FORMAT('ClassData'[Time Inv], "General Number"),"Current Music Team", 'ClassData'[Music Team],"Current Instrument", 'ClassData'[Instrument]),SELECTCOLUMNS('Previous Month Data',"YearMonth", FORMAT('Previous Month Data'[PreviousYearMonth], "YYYYMM"),"Student Number", FORMAT('Previous Month Data'[Student Number], "General Number"),"Previous Final Register", FORMAT('Previous Month Data'[Final register], "General Number"),"Previous Time Inv", FORMAT('Previous Month Data'[Time Inv], "General Number"),"Previous Music Team", 'Previous Month Data'[Music Team],"Previous Instrument", 'Previous Month Data'[Instrument]))
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Hi @Charcho ,
we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
B Manikanteswara Reddy- Charcho
Thank you for your reply Anonymous . The output has improved compared to the previous version, but it's still not completely accurate. Some additional lines, such as those marked in red, contain incorrect or misleading data. Example with Julen:
Hi Charcho ,
Apologies for the delay, and thank you for your patience.
I've made some updates to the report based on your feedback. Specifically, I created a new NextMonthKey in Power Query within the Previous Month Data table. Then, I used this to merge with the main table using Student Number and the DateKey (from the current month) and NextMonthKey (from the previous month table).
Please check the attached .pbix file.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
