Forum Discussion

RaMahi's avatar
RaMahi
Regular Visitor
2 months ago
Solved

MoM calculation

Hi Team,

I need to calculate the previous month's score so that when a month is selected from the slicer, the conrresponding previous month's score is displayed against each country.

Interim score_avg is a measure which is calculated as below: 

Interim score_avg = AVERAGEX(VALUES(E1[MMYYYY]),CALCULATE(SUM(E1[Interim Score (A*B)])))

 

MoM calculation
  • krishnakanth240's avatar
    krishnakanth240
    2 months ago

    Hi RaMahi 

    As you have the measures for Current month(Interim score_avg) and Previous month(Interim score_avg PM) based on the Month difference you can show the arrow directions

    MoM Change = [Interim score_avg] - [Interim score_avg PM]


    MoM Arrow =
    SWITCH(
    TRUE(),
    [MoM Change] > 0, "▲",
    [MoM Change] < 0, "▼",
    "-")

     

6 Replies

  • Shai_Karmani's avatar
    Shai_Karmani
    Super User
    2 months ago

    For previous month time intelligence, you need a Date table with a real Date column marked as the date table, related to E1 on a Date column (not MMYYYY text). Once that is in place, this measure gives the previous month's score per country:

    Interim score_avg PM = 
CALCULATE(
    [Interim score_avg],
    DATEADD('Date'[Date], -1, MONTH)
)

    If E1 has a real date column you can use directly, the same DATEADD on E1's date will work. A marked Date table is cleaner because slicers on E1[MMYYYY] (text) cannot drive DATEADD; the shift has to happen on a real date column. Put your month slicer on the Date table so the row context for the previous month is unambiguous.

     

    If that was helpful, please give a thumbs up and mark it as resolved.

     

    Best,
    Shai Karmani

     

    Let's connect in LinkedIn

    • RaMahi's avatar
      RaMahi
      Regular Visitor
      2 months ago

      Hello Shai_Karmani ,

      Thanks for the solution.

      However This solution is showing the last month's scorem but when I select a date from the slicer, it become Blank. Could you please suggest a solution for this?

       

      Actually, I want to display an up/down arrow in the "Interim score_avg" column, which is why i am considering this solution.

      • krishnakanth240's avatar
        krishnakanth240
        Super User
        2 months ago

        Hi RaMahi 

        As you have the measures for Current month(Interim score_avg) and Previous month(Interim score_avg PM) based on the Month difference you can show the arrow directions

        MoM Change = [Interim score_avg] - [Interim score_avg PM]


        MoM Arrow =
        SWITCH(
        TRUE(),
        [MoM Change] > 0, "▲",
        [MoM Change] < 0, "▼",
        "-")

         

  • Murtaza_Ghafoor's avatar
    Murtaza_Ghafoor
    Super User
    2 months ago

    RaMahi 
    You need to add DAX measure like dateadd, that will calculate the previous month but you need to have proper date table in your data model, then you can build the calculation, as an example similar to following DAX measure will help you to achiveve the goal.

      

    Previous month score=CALCULATE([Interim score_avg],DATEADD('DateTable'[Date],-1,MONTH)

    This will:

    Respect the selected country.

    When June 2026 is selected, return May 2026's score.

    When displayed in a matrix by country, show the previous month's score for each country.


    If this helps, ✓ Mark as Kudos | Help Others

  • v-abhinavmu's avatar
    v-abhinavmu
    Community Support
    2 months ago

    Hi RaMahi,

    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. 


    Thank you.