Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

new Amount = CALCULATE(SUM(BalanceSheet[Amount]),FILTER(BalanceSheet,BalanceSheet[Dates]=MAX(BalanceSheet[Dates])))
Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

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

new Amount = CALCULATE(SUM(BalanceSheet[Amount]),FILTER(BalanceSheet,BalanceSheet[Dates]=MAX(BalanceSheet[Dates])))
Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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

amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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
Not applicable

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

amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.