Forum Discussion
Helper ITotal error when Retrieve the last available value from a table.
Community ChampionHi Ezzeldin when
1. Table Date is set active
2. Create new measure for simple sum of Facility limit (total 4198257312) as following
you will get total per year on Output
Output
Helper IDear 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?
- Ezzeldin
is use "Enter data" option for every table in the file to allow any one with the file see the data
- 1. "balance carrying amount" / last value per GL account?
**Not the carrying amount only the last value available of a GL.
- 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?
**Yes
- some_bih
Hi Ezzeldin
Check enclosed file. I created two measures as below.
I check for two GL ID's as shown on picture below
Sum_simple sum =--simple sum formulaSUM(BRU_Facility_Limit[Facility_Limit])Sum adjusted =IF (ISEMPTY ( BRU_Facility_Limit ),CALCULATE ( [Sum_simple sum], OFFSET ( -1,, ORDERBY ( 'DATES'[Date] ), ) ),[Sum_simple sum]) - Ezzeldin
if gives me an error.
- Ezzeldin
Unfortunately, the result is still incorrect.
- Ezzeldin
hi some_bih ,
Based on the screenshot provided, I tested GL 102122. When I selected Year 2023 and Month 1 for Company Code 1000, the expected result was 200,000,000. Similarly, for Company Code 2000, the expected result was 300,000,000. The total amount was expected to be 500,000,000.
- Ezzeldin
200,000,000 is the last available value for GL: 102122 in company code: 1000 when we choose Year: 2023 and Month: 1.
if we choose Year: 2022 and Month: 2 the last available value for GL: 102122 in company code: 1000 is:165,000,000same for company code: 2000 when we choose Year: 2023 and Month: 1 the result for GL: 102122 should be: 300,000,000 which is the last available value before the last date of the selected month as my measure dose.
the problem with my measure is with the totals.