Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate average from prior months

Hi, 

 

I am relatively new to power bi. So was hoping someone can help me out to do this.

 

I have a monthly sets of data and I need to calculate the average for the current month, using the previous month and current month.

How do i achive this using measures? or do i need to calculate under 'edit queries' and how?

MonthAmountAverage

Jan

2000

-

Feb8001400
March 600700
April1000800

 

Appreciate anyone's help on this.

 

Thanks

 

Need Help
Tips and Tricks
  • az38's avatar
    az38
    6 years ago

    Hi   Anonymous

    first, its not a good idea to keep only month name in the Month field. for example, you could use last day of month here

    if so, then you will need to create a measure (not query editor mode)

    Average = (calculate(sum(Table1[Amount]);PREVIOUSMONTH(Table1[Month]))+calculate(sum(Table1[Amount]);ALLEXCEPT(Table1;Table1[Month])))/2

    do not hesitate to give a kudo to useful posts and mark solutions as solution

     

     

1 Reply

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi   Anonymous

    first, its not a good idea to keep only month name in the Month field. for example, you could use last day of month here

    if so, then you will need to create a measure (not query editor mode)

    Average = (calculate(sum(Table1[Amount]);PREVIOUSMONTH(Table1[Month]))+calculate(sum(Table1[Amount]);ALLEXCEPT(Table1;Table1[Month])))/2

    do not hesitate to give a kudo to useful posts and mark solutions as solution