Forum Discussion

henryhuynhpham's avatar
henryhuynhpham
New Member
2 years ago
Solved

Month to Month Difference

I am doing a dashboard showing percentage score of each month for different elements. I am stuck at adding a comparision/different from this month to the previous month. For example, August's overall is 45% and July's overall is 40% and I want to show the 5% different in the Card or Bar Chart Visualization. Also, is it possible to make an up or down arrow to indicate that instead of the minus sign?

 

Need Help
  • Nilupul's avatar
    Nilupul
    2 years ago

    This is because Month column data type is not a date.
    You can try to change the Month column data type to Date in transform data option.
    Right click on the table particular table in Data section > go to edit query
    Select the month column
    Go to Transform tab in the top menu.
    change Data Type to “Date"

     

    Once change it, Click on File > Close and Apply


    After that try to create the measure.

5 Replies

  • Nilupul's avatar
    Nilupul
    Frequent Visitor
    2 years ago

    You can try below measure to calculate the diffrence between months.

     

    Overall Score Diffrence = 
VAR _CurrentMonthScore = SUM('Table'[Score])
VAR _PrevMonthScore =
CALCULATE(SUM('Table'[Score]),PREVIOUSMONTH('Table'[Month]))

RETURN
_CurrentMonthScore - _PrevMonthScore

     

     

      • Nilupul's avatar
        Nilupul
        Frequent Visitor
        2 years ago

        This is because Month column data type is not a date.
        You can try to change the Month column data type to Date in transform data option.
        Right click on the table particular table in Data section > go to edit query
        Select the month column
        Go to Transform tab in the top menu.
        change Data Type to “Date"

         

        Once change it, Click on File > Close and Apply


        After that try to create the measure.

  • Nilupul's avatar
    Nilupul
    Frequent Visitor
    2 years ago

    You can use below query to indicate negative values with down arrow key, positive value with up arrow key

    Overall Score Diffrence = 
VAR _CurrentMonthScore =
    SUM ( 'Table'[Score] )
VAR _PrevMonthScore =
    CALCULATE ( SUM ( 'Table'[Score] ), PREVIOUSMONTH ( 'Table'[Month] ) )

VAR Diff = _CurrentMonthScore - _PrevMonthScore

RETURN
    IF (
        Diff < 0,
        CONCATENATE ( UNICHAR ( 9660 ), FORMAT ( ABS ( Diff ), "Percent" ) ),
        CONCATENATE ( UNICHAR ( 9650 ), FORMAT ( Diff, "Percent" ) )
    )

     

    Additionally, You can use conditional formatting for values like below to show negative values with another color ( ▼ )

    conditional formatting for callout value