Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate Run Rate

Hello all, 

 

May i know how to write a DAX for Run Rate = ( Total Net Sales/9 month)*12 in power BI? 

i would like the date is automate means if going oct so the total run rate will calculate (total net sales/10month)*12. 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    Here I create a sample to have a test. I suggest you to create a Calendar table to help your calculation.

    Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthShortName",FORMAT([Date],"MMM"))

    Data model:

    Measure:

    Net Sales = CALCULATE(sum('Table'[Value]))
    Running Rate = 
VAR _LASTMONTH =
    CALCULATE (
        MAX ( Calendar[Month] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Year] = MAX ( 'Calendar'[Year] )
                && [Net Sales] <> 0
        )
    )
VAR _Total =
    CALCULATE ( [Net Sales], ALLEXCEPT ( 'Calendar', 'Calendar'[Year] ) )
RETURN
    DIVIDE ( _Total, _LASTMONTH ) * 12

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

3 Replies

  • ChiragGarg2512's avatar
    ChiragGarg2512
    Solution Sage
    2 years ago

    Anonymous , Use of function average or averagex should work in this case. Otherwise provide data in tabular form.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Here I create a sample to have a test. I suggest you to create a Calendar table to help your calculation.

    Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthShortName",FORMAT([Date],"MMM"))

    Data model:

    Measure:

    Net Sales = CALCULATE(sum('Table'[Value]))
    Running Rate = 
VAR _LASTMONTH =
    CALCULATE (
        MAX ( Calendar[Month] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Year] = MAX ( 'Calendar'[Year] )
                && [Net Sales] <> 0
        )
    )
VAR _Total =
    CALCULATE ( [Net Sales], ALLEXCEPT ( 'Calendar', 'Calendar'[Year] ) )
RETURN
    DIVIDE ( _Total, _LASTMONTH ) * 12

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, I need the same but for month

      for example

      in excel I have the formula

      %compliance/daysworked*days of the month

      in excel I have the formula
% compliance/days worked*days of the month

      I try to do the same in power bi but is not working.

      Can you help me please.