Forum Discussion
Helper ITotal error when Retrieve the last available value from a table.
Community ChampionHi Ezzeldin
Please check your Dates table relationship, usually it should be active and after that play again with dates year and months.
Helper IHi some_bih ,
To clarify, the measure's output wasn't behaving as required, so I made the relationship inactive to obtain the last available value per GL. However, the issue is not with the retrieved values themselves, but rather with the totals displayed in the visual.
Thank you in advance for your assistance.
- some_bih
Hi Ezzeldin when
1. Table Date is set active
2. Create new measure for simple sum of Facility limit (total 4198257312) as following
Sum_simple sum =--simple sum formulaSUM(BRU_Facility_Limit[Facility_Limit])you will get total per year on Output
Output
- Ezzeldin
Dear some_bih ,
the business requirements are as follows:
the business enters a limit for each GL whenever changes happen to the limit of the GL.
So the limit value for each GL does not exist for each month, and the business needs to view in the visual all the GLs in the table with the last value limit entered before the selected Month even if there is no value for the GL in the selected month.
Example:
If the user selects Year: 2023 and Month: 1
Then
the value of the GL: 9999 in the visual should be: 60,000 and the value of the GL: 8888 in the visual should be: 90,000
So the total for the company code: 1000 equals: 150,000 and the Row Total = 150,000
Similarly, If the user selects Year: 2022 and Month:12
Then
the value of the GL: 9999 in the visual should be: 30,000 and the value of the GL: 8888 in the visual should be: 90,000
So the total for the company code: 1000 equals: 120,000 and the Row Total = 120,000
That’s what I could manage, by making the relationship inactive and by the measure I did. but it is not behaving right with the totals per company code and the Total row. So I want the GL rows for Year: 2023 & Month: 1 to be the same in the screenshot and the Power BI file but the Totals I want it to be as in the below screenshot
Thanks in advance.
- some_bih
Hi Ezzeldin
I could not see your data example in file you provided, table BRU_facility_limit or GL account
Still, you want to see (I work in accounting and reporting :))
- 1. "balance carrying amount" / last value per GL account?
- 2. amounts should be combined meaning if you select 2023 and February per visual it should be shown not only 2023 and Februar but other last amount?