Forum Discussion

kenyaherring93's avatar
kenyaherring93
Helper I
5 months ago
Solved

Need help setting up new table in BI

Hello, I have this dataset below in a spreadsheet

But i want to figure out a way to get a one month differential with a new table or another way so it looks like this

There will be a record for each system everyone month

Need Help
Show and Tell
Tips and Tricks
  • MasonMA's avatar
    MasonMA
    5 months ago

    If you already have a Date table that is filtering Fact table (this table)

     

    This can be done with a couple of Measures like below, 

    Weighted Avg =
    AVERAGE ( Fact[Weighted Average] )

     

    WA_2026_02 =
    CALCULATE (
        [Weighted Avg],
        Date[Date] = DATE ( 2026, 2, 9 ) // create another for WA_2026_03
    )
     

    MoM Differential =
    VAR Previous = [WA_2026_02]
    VAR Current  = [WA_2026_03]
    RETURN
    IF (
        NOT ISBLANK ( Previous ),
        DIVIDE ( Current - Previous, Previous )
    )

     

    Then use a Table visual with System Name for Rows and WA_2026_02, WA_2026_03, MoM Differential for Values. 

     

     

     

6 Replies

  • MasonMA's avatar
    MasonMA
    Super User
    5 months ago

    If you already have a Date table that is filtering Fact table (this table)

     

    This can be done with a couple of Measures like below, 

    Weighted Avg =
    AVERAGE ( Fact[Weighted Average] )

     

    WA_2026_02 =
    CALCULATE (
        [Weighted Avg],
        Date[Date] = DATE ( 2026, 2, 9 ) // create another for WA_2026_03
    )
     

    MoM Differential =
    VAR Previous = [WA_2026_02]
    VAR Current  = [WA_2026_03]
    RETURN
    IF (
        NOT ISBLANK ( Previous ),
        DIVIDE ( Current - Previous, Previous )
    )

     

    Then use a Table visual with System Name for Rows and WA_2026_02, WA_2026_03, MoM Differential for Values. 

     

     

     

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    5 months ago

    Hi kenyaherring93  ,
    Thanks for reaching out to Community Forum.

    The task is to calculate Month-over-Month (MoM) changes for each system and present them in a structured format, which involves time intelligence. Instead of using hardcoded dates or manually finding the latest and previous records, I added a date dimension and linked it to the fact table by Pull Date. This lets the model automatically identify current and prior periods. I then created measures for current value, previous period value, and MoM percentage change. For visualization, I used a matrix with System Name as rows and Month as columns, displaying both the value and MoM%. To keep the results relevant, MoM% is shown only for the latest month, avoiding blanks or misleading comparisons for earlier periods. This method keeps the solution dynamic, compatible with filters and slicers, and scalable as new data is added.
    The PBIX file is attached for your review. Hope this helps
    Thank you.

    • v-sshirivolu's avatar
      v-sshirivolu
      Community Support
      5 months ago

      HI kenyaherring93 

      I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

       

      • v-sshirivolu's avatar
        v-sshirivolu
        Community Support
        5 months ago

        Hi cengizhanarslan ,

        I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

         

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    5 months ago

    Try the measures as below:

    Weighted Avg = AVERAGE('Table'[Weighted Average])

Weighted Avg PM =
CALCULATE (
    [Weighted Avg],
    DATEADD ( 'Date'[Date], -1, MONTH )
)

Month Diff % =
DIVIDE ( [Weighted Avg] - [Weighted Avg PM], [Weighted Avg PM] )