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

[Anonymous]

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.

[amitchandak · Accepted solution]

@Anonymous , Create a measure in the balance sheet table too

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

[Anonymous]

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

[amitchandak]

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

[Anonymous]

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?

[Anonymous]

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

[amitchandak]

@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

[amitchandak]

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

[Anonymous]

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.