Forum Discussion

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

if gives me an error.

 

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Ezzeldin this is known bug. Dont worry, check results

  • Ezzeldin's avatar
    Ezzeldin
    Helper I
    2 years ago

    Unfortunately, the result is still incorrect.

  • some_bih's avatar
    some_bih
    Community 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
    Helper 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
    Community 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
    Helper 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.