Forum Discussion

tasmiaa's avatar
tasmiaa
Advocate I
7 years ago
Solved

DAX question about static vs dynamic calculation

*I'm simplifying the math involved and the numbers aren't really representative of what I'm calculating 

 

In Excel,

1. I have a calculation for a 3 Month Average Rate. The calculation takes the average of the last 3 months and returns a value. 

 

 janfebmarch
num526
den10235
rate0.50.0869571.2

 

3 Month Average Rate = 

0.595652

 

2. I have a second calculation for that is 3 Month Average Rate * den 

 

 janfebmarch
num526
den10235
rate0.50.0869571.2
calc25.95652213.72.978261

 

The 3 Month Average Rate stays static, but the den changes by month.

 

In Power BI,

1. I have a 3 Month Average Rate measure that calculates the average from the last 3 months. 

 

3 Month Average (%) = CALCULATE(SUM('table'num)/Sum('table'den),DATESINPERIOD('Date'[Date], [Selected Date], -3, MONTH))

2. I need to make a second measure that calculates 3 Month Average (%) * den, but the 3 Month Average (%) should be static and the den should be associated with the month it aligns to. How do I create a DAX measure that doesn't recalculate the 3 Month Average (%) by month and multiplies that by the changing den by month?

 

  • Hi tasmiaa,

     

    You can calculate the 3 Month Average Rate with a calculated column. While create a measure to multiply it with dynamical "den" values.

     

    Regards,

    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee

    Hi tasmiaa,

     

    You can calculate the 3 Month Average Rate with a calculated column. While create a measure to multiply it with dynamical "den" values.

     

    Regards,

    Yuliana Gu