Forum Discussion
Create Measures with Circular Dependency.
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.
Please see the sample pbix in my previous reply which was based solely on the initial data provided.
6 Replies
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.
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.Please see the sample pbix in my previous reply which was based solely on the initial data provided.
Hi SrivatsaB
On reply to the danextian
Could you please share sample pbix file or esle share the sample data,
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 SrikanthHi SrivatsaB
Sorry fo the late response.
Could you please share more details of your requirement or else sample data. Based on the data I will work and provide the pbix file for you address your issue efficiently.
Best Regards,
Cheri SrikanthHi SrivatsaB
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!Best Regards,
Community Support Team _ C Srikanth.
