topic Re: Total error when Retrieve the last available value from a table. in DAX Commands and Tips

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

Ezzeldin — Tue, 19 Dec 2023 12:51:59 GMT

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 table called "BRU_Facility_Limit" that has a value for each GL_ID in different Company_Code. as per the below screenshot.

I created a report page containing the following:
Year slicer that comes from the DATES table
Month Number slicer that comes from the DATES table

both of these slices filter a table Matrix that has in Rows a Company_Code column from the "COMPANYCODE" table & GL_ID from the "GL_Accounts" table to allow drill down from company code to its GL IDs.

I created a measure that retrieves the last available value of the GL in the "BRU_Facility_Limit" table because the values in this table are not continuous for each month. so if the user chooses Year: 2023 & Month:1 and there are no values for the GL; the measure will return the values of the last available month before the selected Year & Month. To do so I make the relationship between the DATES dimension table and the BRU_Facility_Limit table "inactive"

The problem is that the Total rows don't sum the total values of the retrieved rows in the visual, it shows the value of the last retrieved available row. the same when I drill up to the Company Code level it doesn't show the Sum of the retrieved GLs but represents the last value of the last retrieved GL.
this is the measure I used:

Sum_Facility_Limit =

VAR EndDateOfMonth = ENDOFMONTH(DATES[Date])

VAR MaxDateForAccount =

CALCULATE(

MAX(BRU_Facility_Limit[Date]),

BRU_Facility_Limit[Date] <= EndDateOfMonth

)

RETURN

SUMX(

VALUES(BRU_Facility_Limit[GL_ID]),

CALCULATE(

SUM(BRU_Facility_Limit[Facility_Limit]),

BRU_Facility_Limit[Date] = MaxDateForAccount

)

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

some_bih — Mon, 18 Dec 2023 14:33:57 GMT

Hi Ezzeldin

if you want to retriev LASTDATE, try to rework your measure as on link for column "Last date Sales" in example I created.

https://dax.do/0qEUdVul7CvnFj/

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

Ezzeldin — Tue, 19 Dec 2023 09:00:18 GMT

hello some_bih ,

Unfortunately, it is not working, as I need in the report.

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

some_bih — Tue, 19 Dec 2023 10:12:20 GMT

Hi Ezzeldin please share file with example and expected output.

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

Ezzeldin — Tue, 19 Dec 2023 10:20:54 GMT

I wanted to attach the Power BI file, but I couldn't find a way to share or attach it to the main question. Can you please guide me on how to share the file?

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

some_bih — Tue, 19 Dec 2023 12:21:30 GMT

Hi Ezzeldin use some of "share" programs like Office 365, Dropbox, Google Drive...sharing link (hide sensitive info)

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

Ezzeldin — Tue, 19 Dec 2023 12:54:26 GMT

done, please check the main post.

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

some_bih — Tue, 19 Dec 2023 13:25:53 GMT

Hi Ezzeldin I wil check it and let you know.

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

some_bih — Wed, 20 Dec 2023 07:24:47 GMT

Hi Ezzeldin

Please check your Dates table relationship, usually it should be active and after that play again with dates year and months.

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

Ezzeldin — Wed, 20 Dec 2023 08:14:39 GMT

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

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

some_bih — Wed, 20 Dec 2023 08:44:59 GMT

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 formula

SUM(BRU_Facility_Limit[Facility_Limit])

you will get total per year on Output

Output

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

Ezzeldin — Wed, 20 Dec 2023 10:54:26 GMT

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.

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

some_bih — Wed, 20 Dec 2023 11:30:46 GMT

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?

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

Ezzeldin — Wed, 20 Dec 2023 12:14:04 GMT

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

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

some_bih — Wed, 20 Dec 2023 12:26:58 GMT

Hi Ezzeldin I will take a look and provide answer.

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

some_bih — Wed, 20 Dec 2023 13:05:54 GMT

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

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

Ezzeldin — Wed, 20 Dec 2023 13:07:51 GMT

Hi some_bih, not a must.

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

some_bih — Thu, 21 Dec 2023 13:08:20 GMT

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]

)

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

Ezzeldin — Mon, 25 Dec 2023 08:15:33 GMT

if gives me an error.

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

some_bih — Mon, 25 Dec 2023 10:29:22 GMT

Ezzeldin this is known bug. Dont worry, check results