Create Measures with Circular Dependency.

[SrivatsaB]

I am trying to convert an excel data output to PowerBI and the measures actually have circular dependency. Can someone help me with a solution?

1.Beginning for first month has to be derived from table with certain condition, from second month it has to pick previous month ending value.
2. Ending is sum of all the rows above for that month, Beginning + M1+M2+M3+M4, every ending value has to be sum of all the rows above from corresponding month.
3.M1, M2,M3,M4 - all the these are direct aggregations from table with a criteria.

Month is a selector the end user, they can choose whatever month they want and the month columns has to start from that selected month till the end of that year.

I am looking for a DAX to calculate Beginning and Ending measures. I am open to ideas to tweak this from data end too.
Thank you.

[v-csrikanth]

Hi @SrivatsaB 
On reply to the @danextian 
We haven’t heard back since our last response and wanted to check if your query has been resolved. If not, please feel free to reach out for further assistance. If it has been resolved, kindly mark the helpful reply as the solution to make it easier for others to find. A kudos would also be greatly appreciated!
Thank you.
Cheri Srikanth

[SrivatsaB]

Thanks for your response @danextian . You are right on Ending, we don't have that in table.

I have Beginning , Category A & Category B from the table. Category C is based out of a whatif parameter, Category D is the previous month value.

Now my ending is, Beginning + Category A + Category B + Category C + Category D. 
This ending has to be called up to the February Beginning section.

[danextian]

Please see the sample pbix in my previous reply which was based solely on the initial data provided.

[danextian]

Hi @SrivatsaB 

Calculating the beginning, YTD and ending balances is pretty simple but what complicates your use case is you are attempting to add an extra row (beginning or possibly ending) which most likely doesn't exist in your fact  table. Matrix in Power BI works just like a Pivot table in Excel, you can't just add add custom rows/columns and still make it work like you are editing a range of cells.

Attached is a sample pbix to help you get started with.