Calculate Monthly Amount for the Year Where the First Month has the Full Amount

[callenbkd]

Greetings! 

I have a data set where the budget amount is stored in the first period of each year. 

We need a monthly budget amount for each period which can be the amount in the first period divided by 12. We'd also want to not include the first period's original amount. 

For example, if the first period amount is 1,200, the monthly amount would be 100. 

Is it possible to use DAX to do this or would it be better to create the records by month? 

[BA_Pete · Accepted solution]

Hi @callenbkd ,

It is perfectly possible to do this in DAX, and I'd even dare to say it's fairly simple too. However, in terms of both performance efficiency and reusability, I'd recommend pushing operations like this as far away from the end user as possible i.e. do it structurally as far back in the process as possible. In order of preference:

-1- As part of your data warehouse (DWH) ETL process.

-2- As a materialised (Stored Procedure) table in your DWH.

-3- As a view on your server.

-4- As a dataflow.

-5- As a Power Query transformation in your Semantic Model.

-6- As a DAX calculation.

However, as always, there's many more variables that can affect this decision that only apply to your specific situation and scenario, such as data quantity/volume and DWH/server access and permissions.

In summary I'd say that if it's a one-off report for a specific purpose that isn't going to be refreshed or reused, do it in DAX, if it's an ongoing requirement that will be reusable by other reports, then do it in DWH or PQ.

Let me know what you think the best solution for your situation will be and I'll help you achieve that.

Pete

[callenbkd]

Thanks. I'm going to go ahead and create a SQL view to handle it.