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 (to...
  • 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.

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.