Forum Discussion

Ezzeldin's avatar
Ezzeldin
Icon for Helper I rankHelper I
2 years ago

Total error when Retrieve the last available value from a table.

Dears, This is a link for the Power BI file of the below-demonstrated Case: https://www.dropbox.com/scl/fi/8d5sz6w4ksjhr6z9312dr/Use-Case.pbix?rlkey=nytwo1qj4w3tylaatarttqog6&dl=0   I have a tabl...
some_bih's avatar
some_bih
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
Ezzeldin
Icon for Helper I rankHelper I
2 years ago

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's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Ezzeldin I will take a look and provide answer.

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Ezzeldin your data are always on end of month level or not?

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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 formula
    SUM(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's avatar
    Ezzeldin
    Icon for Helper I rankHelper I
    2 years ago

    if gives me an error.

     

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Ezzeldin this is known bug. Dont worry, check results

  • Ezzeldin's avatar
    Ezzeldin
    Icon for Helper I rankHelper I
    2 years ago

    Unfortunately, the result is still incorrect.

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Ezzeldin give me example of wrong and one example of correct /expected output based on enclosed file

  • Ezzeldin's avatar
    Ezzeldin
    Icon for Helper I rankHelper I
    2 years ago

    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.

     

     

     

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Ezzeldin 

    I filtered out only GL 102122 below how to get 200 000 for January 2023. Mark rows

     

  • Ezzeldin's avatar
    Ezzeldin
    Icon for Helper I rankHelper I
    2 years ago

    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,000

    same 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.