Replicating Excel calculation in power bi

[MrMP]

Hi All,

I am trying to replicate the excel formula in power bi. 

Here is the structure:

The only known is sales per month (yellow row). Other fields are calculations. Is it possible to create something like this in power bi?

I am attaching excel for easier formula exploration.

Hope you can help! Thank you!

[Shaurya]

Hi @MrMP,

I have checked your excel file and I think you easily create all those fields in Power BI. I suggest that you go for DAX Columns. You should with creating Beginning Rec by using something like:

Beginning Rec = IF('Table'[Month]=EARLIER('Table'[Month])+1,'Table'[Ending Receivable],null)

Then you can go on and create the other fields which are arithmetic operations like addition or multiplication.

Did I answer your question? Mark this post as a solution if I did!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

[MrMP]

@Shaurya Do you have any idea how this can be achived with measures?

[Shaurya · Accepted solution]

Hi @MrMP,

Since your formulas require that you reference various calculated fields based on a row level context, I really think you should go for columns. You can write conditions for the % field if it is quantised.

Did I answer your question? Mark this post as a solution if I did!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

[MrMP]

Thank you for prompt answer, but all this has to stay in measures since % field is controlled by numeric parameter.