Forum Discussion

Jacky_2022's avatar
Jacky_2022
New Member
3 years ago
Solved

Average calculation of two specific comparative values

Hi all,

I am an absolute rookie in Power BI and I don't know what to do with the following case.

 

I want to calculate the average value from two month-over-month change values. But the comparative month should always be from the previous year.

 

Means:

Comparative value 1: July 2021 to July 2020

Comparative value 2: July 2022 to July 2021

 

And from these two values the average is to be calculated:
(Comparative value 1 + Comparative value 2) / 2

 

The quick measure for a month-over-month-change calculation already exists:

products per order MoM% =

VAR _lastyear_MONTH = CALCULATE([products per order], DATEADD('Date'[Date], -12, MONTH))

RETURN

DIVIDE([products per order] - _lastyear_MONTH, _lastyear_MONTH)

 

I have the value "products per order" for each month from July 2020 to October 2022. This means that the quick measure calculates the months from today back to my last data entry. I just don't know how I can calculate in a measure that exactly these two comparison values (see above) are taken into the calculation of the average.

 

My overall goal is, to use the calculated averge of the change values to do a forecast calculation for the months July 2023, August 2023 and Oktober 2023.

 

Thanks for your help!

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Jacky_2022 

     According to your description, you want to calculate the "Average calculation of two specific comparative values". And the date is this:

    Comparative value 1: July 2021 to July 2020

    Comparative value 2: July 2022 to July 2021

    Here are the steps you can refer to :
    (1)This is my test data:

    (2)We can create a  calendar date table like this:

    Calendar = ADDCOLUMNS(
CALENDAR(FIRSTDATE('Table'[Date]),LASTDATE('Table'[Date]))
,"year_month" , YEAR([Date])*100+MONTH([Date]))

    And make relationship to our table like this:

    (3)Then we can create a measure like this:

    Measure = var _202107_value = CALCULATE( SUM('Table'[Value]) ,  FILTER( ALL('Calendar') , 'Calendar'[year_month] = 202107))
var _202007_value = CALCULATE( SUM('Table'[Value]) ,  FILTER( ALL('Calendar') , 'Calendar'[year_month] = 202007))
var _202207_value = CALCULATE( SUM('Table'[Value]) ,  FILTER( ALL('Calendar') , 'Calendar'[year_month] = 202207))
return
DIVIDE((_202107_value -_202007_value)+(_202207_value-_202107_value),2)

    (4)Then we can put this measure on the visual and we can meet your need , the result is as follows:

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

     

2 Replies

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

    Jacky_2022 , The above should have worked. or also create current month meausre

     

    MTD Sales = CALCULATE([products per order],DATESMTD('Date'[Date]))


    last year MTD Sales = CALCULATE([products per order], DATESMTD(dateadd('Date'[Date],-12,MONTH)))
    Previous year Month Sales = CALCULATE([products per order], previousmonth(dateadd('Date'[Date],-11,MONTH)))
    last year MTD (complete) Sales = CALCULATE([products per order] ,DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

     

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

    Power BI — Month on Month with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
    https://www.youtube.com/watch?v=6LUBbvcxtKA

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi , Jacky_2022 

     According to your description, you want to calculate the "Average calculation of two specific comparative values". And the date is this:

    Comparative value 1: July 2021 to July 2020

    Comparative value 2: July 2022 to July 2021

    Here are the steps you can refer to :
    (1)This is my test data:

    (2)We can create a  calendar date table like this:

    Calendar = ADDCOLUMNS(
CALENDAR(FIRSTDATE('Table'[Date]),LASTDATE('Table'[Date]))
,"year_month" , YEAR([Date])*100+MONTH([Date]))

    And make relationship to our table like this:

    (3)Then we can create a measure like this:

    Measure = var _202107_value = CALCULATE( SUM('Table'[Value]) ,  FILTER( ALL('Calendar') , 'Calendar'[year_month] = 202107))
var _202007_value = CALCULATE( SUM('Table'[Value]) ,  FILTER( ALL('Calendar') , 'Calendar'[year_month] = 202007))
var _202207_value = CALCULATE( SUM('Table'[Value]) ,  FILTER( ALL('Calendar') , 'Calendar'[year_month] = 202207))
return
DIVIDE((_202107_value -_202007_value)+(_202207_value-_202107_value),2)

    (4)Then we can put this measure on the visual and we can meet your need , the result is as follows:

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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