topic Re: last date value in a table for selected period for balance sheet in DAX Commands and Tips

last date value in a table for selected period for balance sheet

Anonymous — Tue, 17 Nov 2020 09:52:46 GMT

Hi, I am trying a custom table for the balance sheet with as on value i.e. the most recent value in the selected period. I have created measures and they are showing the correct value as required but when the measures are being used in the table function, they are not showing correctly the figures.

PBIX File is attached. Refer to the table BalanceSheet for the issue. I have placed the measures inside a card visual beneath the BalanceSheet table matrix. Please see the difference. Thanks in anticipation.

Re: last date value in a table for selected period for balance sheet

amitchandak — Tue, 17 Nov 2020 10:34:21 GMT

Anonymous , there is no card visual on Balancesheet page. What is expected value , what is wrong value

Re: last date value in a table for selected period for balance sheet

Anonymous — Tue, 17 Nov 2020 10:51:37 GMT

Hi Amit,

In the Balance Sheet Analytics page there is a multi-row card with PPE and CWIP value and above same, there is a matrix with the same PPE and CWIP but having different values. The expected values are of the multi-row card values. Thanks in anticipation.

Re: last date value in a table for selected period for balance sheet

amitchandak — Tue, 17 Nov 2020 11:12:03 GMT

Anonymous , PPE, CWIP is filtered on last date. I do not see the same in the case of table. It coming from an aggegated table, where I do not see these measures or filters

Re: last date value in a table for selected period for balance sheet

Anonymous — Tue, 17 Nov 2020 11:19:24 GMT

Sorry Amit, the wrong file got uploaded Please check the file now.......

Re: last date value in a table for selected period for balance sheet

amitchandak — Tue, 17 Nov 2020 14:27:26 GMT

Anonymous , got it. this because when you are using select columns you have taken division and Date. As you are using max(Table[Date]), it will use the row the context of division and date. As the date max date is as good not having it, in this case. Now the new table is created it will be able to sum up rows too .

Re: last date value in a table for selected period for balance sheet

Anonymous — Tue, 17 Nov 2020 14:25:02 GMT

It will still show the incorrect figure even I do not selectcolums for division and date. What am I supposed to do? can u pls help?

Re: last date value in a table for selected period for balance sheet

amitchandak — Tue, 17 Nov 2020 14:47:16 GMT

Anonymous , Create a measure in the balance sheet table too

new Amount = CALCULATE(SUM(BalanceSheet[Amount]),FILTER(BalanceSheet,BalanceSheet[Dates]=MAX(BalanceSheet[Dates])))

Re: last date value in a table for selected period for balance sheet

Anonymous — Tue, 17 Nov 2020 14:52:43 GMT

Thanks a ton! I know it was pretty stupid but I got confused in the row context.