Please help to solve the measure

[Anonymous]

I have a table like below which shows the Invoice amount at various dates. The invoice amount would remain the same between two dates. For ex, amount is 600 on 5-1-2021 and would remain the same till 5-8-2021, at which point it changes to 500. If I want to view the invoice amount for any intermediate date (say 5-13-2021, amount = 500). how do I go about doing this in Power BI.

Ratpay	Invoice	Date
600	1234	5/1/2021
500	1234	5/8/2021
400	1234	5/15/2021
300	1234	5/22/2021
200	1234	5/29/2021
100	1234	6/5/2021
350	5678	5/3/2021
250	5678	5/10/2021
150	5678	5/17/2021
50	5678	5/24/2021

I achieved the result by using the below measure.

Measure =
VAR __sel = maxx(allselected('Date'), 'Date'[Date])
VAR __id = MAX ('Table'[Invoice Number] )
VAR __date = CALCULATE ( MAXX(filter('Table', [Date] <__sel),'Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[Invoice Number] = __id )
CALCULATE ( max ('Table'[CHANNEL] ), VALUES ('Table'[Invoice Number] ),'Table'[Invoice Number] = __id,'Table'[Date] = __date )

The measure works fine for a single and multiple invoice. But how should we arrive at total amount for a set of selected invoices or all invoices put together. Thanks.

[Anonymous · Accepted solution]

My measure, as I said, works perfectly OK.

[Anonymous]

[Total Outstanding] =
var LastVisibleDate = MAX( 'Date'[Date] )
var Result =
    CALCULATE(
        SUMX(
            DISTINCT( Invoices[InvoiceNumber] ),
            CALCULATE(
                MAXX(
                    TOPN(1,
                        Invoices,
                        Invoices[Date],
                        DESC
                    ),
                    Invoices[Ratpay]
                )
            )
        ),
        'Date'[Date] <= LastVisibleDate,
        // This last line is redundant if 'Date'
        // has been marked as a date table in
        // the model.
        REMOVEFILTERS( 'Date' )
    )    
return
    Result

@Anonymous, I would kindly recommend that you invest some time into understanding what ALLSELECTED is for. Here's something that should help you understand: https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

You should also remember that fields from fact tables should never be used for slicing and dicing. Dimensions and only they are suitable for this purpose.

[Anonymous]

The measure works fine for total amount. But how should we arrive at date wise ratpay for a set of selected invoices or all invoices put together. For ex, amount is 600 on 5-1-2021 and would remain the same till 5-8-2021, at which point it changes to 500. If I want to view the invoice amount for any intermediate date (say 5-5-2021, amount = 600). how do I go about doing this in Power BI.

Ratpay	Invoice	Date
600	1234	5/1/2021
500	1234	5/8/2021
400	1234	5/15/2021
300	1234	5/22/2021
200	1234	5/29/2021
100	1234	6/5/2021
350	5678	5/3/2021
250	5678	5/10/2021
150	5678	5/17/2021
50	5678	5/24/2021

Total Outstanding1 =

var LastVisibleDate = MAX( 'Date'[Date] )

var Result =

CALCULATE( SUMX( DISTINCT( Query1[invoice]),

CALCULATE( MAXX( FILTER( Query1, Query1[date]), Query1[ratpay] ) ) ),

'Date'[Date] <= LastVisibleDate,REMOVEFILTERS('Date'))

return Result

I want, if i choose the date(say 5-5-2021, amount = 600) for 1234 invoice no. Thanks

[Anonymous]

My formula gives you everything you want if you slice your data properly.

[Anonymous]

The measure works fine for total amount only. If I want to view the invoice amount for any intermediate date(say 5-5-2021, amount = 600) for 1234 invoice no. Your measure is not gives the date wise ratpay. Please see the screenshot in the below,