How to Calculate a Run Rate based off the Fiscal Day in Month

[Roseventura]

I have a table which shows Daily Bookings and MTD Bookings broken down by fiscal month.  It's a matrix table since I need the months to appear across the top.

The first column is called CADIM which is Day in Month and goes from 1 to 25 (there can be up to 25 days in our fiscal month).

What I'm trying to do is create a measure which takes the MTD Bookings number and divide it by whatever the Day in Month is for that row.

For example (based off the below table), if the MTD Bookings for Day 10 is $8,123,912, then the run rate would be 8,123,912 / 10 = $812,391.20.  If MTD Bookings for Day 19 is $15,871,745 the run rate would be 15,871,745 / 19 = $835,355.

I know how to look up the CADIM field from my DATE table, but it only returns what the current Day in Month (or Today's number) is.

Any help would be greatly appreciated!!

[Roseventura · Accepted solution]

I will try this solution and get back to you.  (Sorry...was on vacation last week.)

[v-juanli-msft]

Hi @Roseventura 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @Roseventura 

If your Fiscal Day in Month is not from 1~25,

but from 3~28 for example,

you want MTD to divide by 1~25, not the actual day number from 3~28, 

If so, you could refer to my previous post which is modified just now.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Roseventura · Accepted solution]

I will try this solution and get back to you.  (Sorry...was on vacation last week.)

[Roseventura · Accepted solution]

Here is the solution that worked for me:

Cumulative MTD Shipments TY Run Rate = [Cumulative MTD Shipments TY] / max(PBI_FSCAPF[CADIM])

Note: (PBI_FSCAPF[CADIM] is the day number in the fiscal month)

Cumulative MTD Shipments TY =

var __minDateVisible = MIN ( PBI_FSCAPF[PADDATE] )

var __maxDateVisible = MAX ( PBI_FSCAPF[PADDATE])

var __currentFiscalMonth = SELECTEDVALUE ( PBI_FSCAPF[PADFMO] )

var __lastTransactionDate = CALCULATE (

         MAX ( PBI_AEP_Shipments_B2B_Multi[Formatted Transaction Date] ),

        ALL ( PBI_AEP_Shipments_B2B_Multi )

    )

var __filter =

    filter (

        all ( PBI_FSCAPF ),

  PBI_FSCAPF[PADDATE] <= __maxDateVisible

    &&

    PBI_FSCAPF[PADFMO] = __currentFiscalMonth

    )

var __cumulative =

CALCULATE (

[Cumulative Shipments TY],

__filter

)

return

    IF (

        __minDateVisible <= __lastTransactionDate,

        __cumulative

    )

Note: PBI_FSCAPF[PADDATE] = synonomous with DATE[Date]

PBI_FSCAPF[PADFMO] = the fiscal month number

[v-juanli-msft]

Hi @Roseventura 

1.

Create a calendar table

calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"day",DAY([Date]))

Create relationship between the calendar table and data table.

2.

Create columns in data table

Actual Day Number = DAY('Table'[Date])

Month = MONTH('Table'[Date])

Year = YEAR('Table'[Date])

Rank Day Number by Month and Year =
RANKX (
FILTER (
'Table',
'Table'[Month] = EARLIER ( 'Table'[Month] )
&& 'Table'[Year] = EARLIER ( 'Table'[Year] )
),
'Table'[Actual Day Number],
,
ASC,
DENSE
)

Create measures

MTD = TOTALMTD(SUM('Table'[Daily Bookings]),'Table'[Date])
Run Rate = [MTD]/MAX('Table'[Rank Day Number by Month])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.