Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

KPI Percentage Change over selected time

Hello, 

 

I need to build a KPI card that shows the percentage difference of the same measure over the selected period of time. For example, I want to show the % increase of exchange rate EURO/USD over the user's selected period of time (example: July 2015 - November 2020). I've tried using the month-over-month change quick measure but that doesn't work because the number of periods needs to be flexible. 

 

Thank you, 

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hello @seferreirama ,

    Please try the following measure: 

    Measure1 =
VAR __PREV_MONTH =
    CALCULATE (
        AVERAGE ( 'Merge1'[Rate] ),
        FILTER (
            'Merge1',
            MONTH ( 'Merge1'[Date] ) = MONTH ( MIN ( 'Merge1'[Date] ) )
                && YEAR ( 'Merge1'[Date] ) = YEAR ( MIN ( 'Merge1'[Date] ) )
        )
    )
RETURN
    DIVIDE (
        CALCULATE (
            AVERAGE ( 'Merge1'[Rate] ),
            FILTER (
                'Merge1',
                MONTH ( 'Merge1'[Date] ) = MONTH ( MAX ( 'Merge1'[Date] ) )
                    && YEAR ( 'Merge1'[Date] ) = YEAR ( MAX ( 'Merge1'[Date] ) )
            )
        ) - __PREV_MONTH,
        __PREV_MONTH
    )

    If this post helps, then consider Accept it as the solution to help other members find it faster.

    Best regards

    Dedmon Dai

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Sample data, and expected output would help

     

    You can month /last month diff like the example with help from date table

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

     

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Would you please try to use DATEDIFF function to change month-over-month change quick measure?

     

     

    EURO/USD MoM% 2 =
IF (
    ISFILTERED ( 'Calendar'[date] ),
    ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." ),
    VAR __PREV_MONTH =
        CALCULATE (
            [EURO/USD],
            DATEADD (
                'Calendar'[date],
                DATEDIFF ( MIN ( 'Calendar'[date] ), MAX ( 'Calendar'[date] ), MONTH ),
                MONTH
            )
        )
    RETURN
        DIVIDE ( [EURO/USD] - __PREV_MONTH, __PREV_MONTH )
)

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi v-deddai1-msft , 

       

      Thank you for the datediff, that works but I'm not getting the expected results. For example, I've selected the exchange rate EUR/BRL and the time period: Jan19 - Dec 19. 

      According to my data Jan 19 = 4,27  and Dec 19 = 4,57. So the calculation should be: (4,57-4,27)/4,27 = 7% .

      However, with the below measure I currently have: 

      Rate MoM% =
      IF(
          ISFILTERED('Merge1'[Date]),
          ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
          VAR __PREV_MONTH = CALCULATE(AVERAGE('Merge1'[Rate]),
      DATEADD('Merge1'[Date].[Date],
      DATEDIFF(MIN('Merge1'[Date].[Date]), MAX('Merge1'[Date].[Date]), MONTH), MONTH))
      RETURN DIVIDE(AVERAGE('Merge1'[Rate]) - __PREV_MONTH, __PREV_MONTH)
      )
       
      the result is -21,94% .
  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hello @seferreirama ,

    Please try the following measure: 

    Measure1 =
VAR __PREV_MONTH =
    CALCULATE (
        AVERAGE ( 'Merge1'[Rate] ),
        FILTER (
            'Merge1',
            MONTH ( 'Merge1'[Date] ) = MONTH ( MIN ( 'Merge1'[Date] ) )
                && YEAR ( 'Merge1'[Date] ) = YEAR ( MIN ( 'Merge1'[Date] ) )
        )
    )
RETURN
    DIVIDE (
        CALCULATE (
            AVERAGE ( 'Merge1'[Rate] ),
            FILTER (
                'Merge1',
                MONTH ( 'Merge1'[Date] ) = MONTH ( MAX ( 'Merge1'[Date] ) )
                    && YEAR ( 'Merge1'[Date] ) = YEAR ( MAX ( 'Merge1'[Date] ) )
            )
        ) - __PREV_MONTH,
        __PREV_MONTH
    )

    If this post helps, then consider Accept it as the solution to help other members find it faster.

    Best regards

    Dedmon Dai