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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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])))

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])))
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 .

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

amitchandak
Super User
Super User

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

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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