Last row of record to show product final balance

[eddiechang]

Hi All,

I trying to have a table that show those product that does not have transaction in last 6 months but there still store balance.

I create 2 measurement to achieve this:

1. Transaction count

Transactions =
IF ( ISBLANK ( [count of transactions] ), 0, [count of transactions] )

After that will only view those with 0.

2. product balance

LastBal = SUMX (
bi_product_master,
CALCULATE (
SUM ( bi_bill[ProductBal] ),
LASTNONBLANK ( bi_bill[DocDate], CALCULATE ( COUNTROWS ( bi_bill) ) )
)
)

However, i faced problem on 2nd measure due to i am using a date slicer to show those product with zero transaction count in 1st measure. Those product have balance before 6 months ago would not shown in table. May i know how to make the 2nd measure ignore the date slicer or always calculate the ALL(bi_bill). Or any alternative measure to fulfill the requirement that always show last row by date of that product balance.

Thanks.

[ChandeepChhabra]

Hi @eddiechang,

Can you share your pbix file ?

[eddiechang]

Hi @ChandeepChhabra,

Thanks for your reply and help offer. pbix here. balance.pbix

Actually there are 2 outcome i wish to have.

1. last row as balance, across whole table without date filter/ slicer filter. For now the product will not show due to there only have transaction within last 6 months.

 

2. Store balance by month. If there no transaction for that particular month, it will not show. Perhaps it can take previous month non blank row, if my logic is not wrong.

Thanks again.