Forum Discussion

Charcho's avatar
Charcho
Icon for Helper I rankHelper I
1 year ago
Solved

Analyze 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 t...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Charcho ,

     

    Steps to Track Monthly Changes in Power BI Using Power Query and DAX:


    --Load the dataset from the Excel file into Power BI.

    Create a Custom DateKey Column

    --Open Power Query Editor.

    In the original table, create a Custom Column from Examples to generate a DateKey in the format Year-Month-01.
    Example output: 2024-11-01.

    Duplicate the Table

    --Right-click the original table and select Duplicate.

    --Rename the duplicated table to PreviousMonthData.

    Create Previous Month Key

    --In the PreviousMonthData table, create the same custom column using the format Year-Month-01.

    --Rename this column to PreviousMonthDateKey.

    Merge the Tables

    --Go to Home > Merge Queries > Merge Queries as New.

    --Merge the original table with PreviousMonthData using a Left Outer Join.

    Join using the

    --Student Number or any common column as

    DateKey from the original table = PreviousMonthDateKey from PreviousMonthData.

    --After the merge, expand the columns from PreviousMonthData to include the fields needed for comparison, such as Instrument, Time Inv, etc.

    --Apply and Load the Data

    --Click Close & Apply to load the merged table into Power BI.

    Create DAX Calculations

    --In the merged table, use DAX to compare the current month and previous month values as needed

    measure:

    FinalChanged = IF(
    [RegistrarChanged] = "Changed" ||
    [InstrumentChanged] = "Changed" ||
    [TimeChanged] = "Changed",
    "Yes", "No"
    )

     

     

    new Calculated columns:

    InstrumentChanged = IF([Instrument] <> [PreviousMonthData.Instrument], "Changed", "Same")


    RegistrarChanged = IF([Final register] <> [PreviousMonthData.Final register], "Changed", "Same")


    TimeChanged = IF([Time Inv] <> [PreviousMonthData.Time Inv], "Changed", "Same")


    A sample Power BI file is attached for reference. Please review and let us know if any adjustments are required.

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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