Calculate run rate and asking rate

[Anonymous]

Hi Experts,

I want to calculate activity run rate and Activity asking hrate based on current month selection from filter pane.

For example - if I am selecting April month from filter pane and today's date is 19/04/2020 then my no. of days will be 19.

1)  So my run rate formula will be like -

= Count(Activity in April till 19)/ 19

So this will be my run rate.

2) So my Asking rate formula will be like -

= ( Activity Target - Activity in April till 19 ) / ( Remaining days in April month)

3) If user select any other month except current month then he should get an message in expression that " Select current month only"

Is that possible ? Kindly help me

[amitchandak]

@Anonymous ,

Try like

Divide(([Activity Target] -CALCULATE(Count('AD_V_Activity_Detail'[Activity_Insert_At]))),datediff(eomonth('AD_V_Activity_Detail'[Activity_Insert_At],0),today(),day))

[Anonymous]

This error is coming with this formula -

[amitchandak]

@Anonymous , Try like

Divide(([Activity Target] -CALCULATE(Count('AD_V_Activity_Detail'[Activity_Insert_At]))),datediff(maxx('AD_V_Activity_Detail',eomonth('AD_V_Activity_Detail'[Activity_Insert_At],0)),today(),day))

[Anonymous]

Thank u so much Amit for prompt help.

This expression is for "Current run rate" or "required run rate" ?  because result is coming wrong

For Haryana in April -

current run rate will be (827-1)/19 = 43.47%

required run rate will be (827-1)/11 = 75.09%

[v-lili6-msft · Accepted solution]

HI  @Anonymous 

You may try this simple formula:

current run rate=
DIVIDE (
    (
        [Activity Target]
            - CALCULATE ( COUNT ( 'AD_V_Activity_Detail'[Activity_Insert_At] ) )
    ),
    DAY ( TODAY () )
)

required run rate=
DIVIDE (
    (
        [Activity Target]
            - CALCULATE ( COUNT ( 'AD_V_Activity_Detail'[Activity_Insert_At] ) )
    ),
    DATEDIFF(TODAY(),EOMONTH(TODAY(),0),DAY)+1
)

Regards,

Lin

[Anonymous]

Thank You So much. 

Working fine 🙂

[Anonymous]

I got below expression from community but it is not giving me MTD days if I select April 2020

Total NumberDaysMonth =

SUMX (

SUMMARIZE (

ADDCOLUMNS (

VALUES ( 'AD_V_Activity_Detail'[Activity_Insert_At] ),

"EOMonth", EOMONTH ('AD_V_Activity_Detail'[Activity_Insert_At] , 0 )

),

[EOMonth]

),

DAY ( [EOMonth] )

)