Power Pivot - DAX code to dynamically calculate Run Rate in Excel based on Months Selected

[hananak]

Dear All,

I am looking for a DAX code in Power Pivot (Pivot Table) to calculate Run Rate dynamically based in the months selected. The Run rate calculation should always exclude the latest month.

There are many (100) Account codes, so the Run Rate calculation is required for each account code.

My months are in columns and called M1 Actual, M2 Actual and so on.

The Financial Year runs from April to March, April being the first month and March being the 12th month.

Please see the picture to understand the outcome.

Your help would be appreciated.

 

[Matt Allington]

This is a time intelligence problem. Have a read of my article here.

DAX Time Intelligence Explained

Level: Beginners I help a lot of people on forums who ask questions about time intelligence for DAX. If you are just starting out then the chances are that you may not even be clear what time intelligence is and hence sometimes you don’t even know what to ask. Often [...]Read More »

exceleratorbi.com.au

Make sure you have a calendar table

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

Create calendar Tables in Power BI

Learn how to create a Power BI calendar table using the basic rules and approach to setting it up daily, weekly or monthly.

exceleratorbi.com.au

 

[hananak]

This is a time intelligence problem. Have a read of my article here.

DAX Time Intelligence Explained

Level: Beginners I help a lot of people on forums who ask questions about time intelligence for DAX. If you are just starting out then the chances are that you may not even be clear what time intelligence is and hence sometimes you don’t even know what to ask. Often [...]Read More »

exceleratorbi.com.au

Make sure you have a calendar table

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

Create calendar Tables in Power BI

Learn how to create a Power BI calendar table using the basic rules and approach to setting it up daily, weekly or monthly.

exceleratorbi.com.au

Thanks for sharing these articles. I have read the first article but unable to find any sloution or guidance on how can I resolve my query.

Could you please guide?

 

[Matt Allington]

Have you created a calendar table with an ID column for month (integer starting at 1 for the first month, 13 for the 13th month, etc) and added it to your model? If not, do that first.
Then add the month from the calendar table to your visual. Don't use the month from your data table.
Then you can write a formula something like this

Monthly Run Rate =
CALCULATE (
    DIVIDE ( SUM ( DataTable[Actual] ), DISTINCTCOUNT ( Calendar[Month ID] ) ),
    FILTER (
        ALL ( Calendar ),
        Calendar[Month ID] < MAX ( Calendar[Month ID] )
            && Calendar[Fin Year] = MAX ( Calendar[Fin Year] )
    )
)