Calculate max of value with gap date

[mh2587]

In this table I want to calculate the Maximum of bank balance of previos 8 days but when Document type is SI  , which will be on documane date  31/12/2022, and bank balance is 2341, i tried this dax formula but its only calculate previous minus 8 days whcih is 6-01-2022, but i need only the previous dates whcih have bank balance. then I want to take amount with SR from previos 8 day add them and subtract them from amount i got form above.

for example = 2341 - (1850+1500) Note == (1850 and 1500 is value with SR whcih is after 31/12/2022) 

Amount due 7 days = VAR previousweek =MAX(CUSTOMER_TRANSACTIONS[DOCUMENT_DATE]) -8

VAR _tbl = CALCULATETABLE(ADDCOLUMNS(SUMMARIZE(CUSTOMER_TRANSACTIONS,CUSTOMER_TRANSACTIONS[DOCUMENT_DATE],CUSTOMER_TRANSACTIONS[Bank Balance],CUSTOMER_TRANSACTIONS[Document Type]),"@average", previousweek2),ALLSELECTED(CUSTOMER_TRANSACTIONS[DOCUMENT_DATE]), ALLSELECTED(CUSTOMER_TRANSACTIONS[Document Type]))

VAR lastmaxdate =MAXX(_tbl, [@average])

VAR amount = CALCULATE(MAX(CUSTOMER_TRANSACTIONS[Bank Balance]),FILTER(_tbl,CUSTOMER_TRANSACTIONS[DOCUMENT_DATE]= lastmaxdate && CUSTOMER_TRANSACTIONS[Document Type]=="SI"))

VAR SR_PAYMENT = CALCULATE ( SUM ( CUSTOMER_TRANSACTIONS[Amount] ),

FILTER (

CUSTOMER_TRANSACTIONS,

CUSTOMER_TRANSACTIONS[Document Type] == "SR"

&&

CUSTOMER_TRANSACTIONS[DOCUMENT_DATE] > lastmaxdate))

VAR Sub = amount - SR_PAYMENT

Return Sub

  Thanks in advance

[mh2587]

Please help me in this @amitchandak @parry2k  @smpa01 

[v-rzhou-msft · Accepted solution]

Hi @mh2587 ,

According to your statement, I find that [DOCUMENT_DATE] is not continuous and there are same dates in it. So we can't use maxdate -8 to get the previos 8 days. Here I suggest you to add a group index column by M code in Power Query Editor.

For reference: Group index in Power Query with M language.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFLDoMwDAXvkjUy/iR2coRu2yXi/tdoQgpVWzeCDbLE8B72LEtgZJ6RZophCo9bfRQ0IKlDZO0DoYV18liyhGCNyVFA0YGxNPje8jK19wldzPbMLATGdRCr/UOUSo+kWICzh/JRXgg4NSSnr/66EM9CR6igbL2xCJgNWZa4x6JkoOTQrPs/ZCwg7Vj8c4NOvu+azEDbRql+Y85mY196xZeOfTG62MjXP9Txpad96QVfn+xW1X3Ra5m3LD0tSy/Iquz6BA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DOCUMENT_DATE = _t, #"Document Type" = _t, #"Bank Balance" = _t, Amount = _t, #"Customer Key" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DOCUMENT_DATE", type date}, {"Document Type", type text}, {"Bank Balance", type number}, {"Amount", type number}, {"Customer Key", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"DOCUMENT_DATE", Order.Ascending}, {"Bank Balance", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Customer Key"}, {{"Rows", each _, type table [DOCUMENT_DATE=nullable date, Document Type=nullable text, Bank Balance=nullable number, Amount=nullable number, Customer Key=nullable number]}}),
    Indexed = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
    #"Expanded Rows" = Table.ExpandTableColumn(Indexed, "Rows", {"DOCUMENT_DATE", "Document Type", "Bank Balance", "Amount", "GroupIndex"}, {"Rows.DOCUMENT_DATE", "Rows.Document Type", "Rows.Bank Balance", "Rows.Amount", "Rows.GroupIndex"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Rows",{{"Rows.DOCUMENT_DATE", "DOCUMENT_DATE"}, {"Rows.Document Type", "Document Type"}, {"Rows.Bank Balance", "Bank Balance"}, {"Rows.Amount", "Amount"}, {"Rows.GroupIndex", "GroupIndex"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Bank Balance", Currency.Type}, {"Amount", Currency.Type}, {"GroupIndex", Int64.Type}, {"DOCUMENT_DATE", type date}})
in
    #"Changed Type1"

I think in your fact table there may be more Customer Keys, so I add 106 for test.

Then create a measure to calculate by your logic.

Amount due 7 days =
VAR _PreviousIndex =
    MAXX (
        ALLEXCEPT ( CUSTOMER_TRANSACTIONS, CUSTOMER_TRANSACTIONS[Customer Key] ),
        CUSTOMER_TRANSACTIONS[GroupIndex]
    ) - 7
VAR _BankBalance =
    CALCULATE (
        SUM ( CUSTOMER_TRANSACTIONS[Bank Balance] ),
        FILTER (
            CUSTOMER_TRANSACTIONS,
            CUSTOMER_TRANSACTIONS[GroupIndex] = _PreviousIndex
                && CUSTOMER_TRANSACTIONS[Document Type] = "SI"
        )
    )
VAR _SR_PAYMENT =
    CALCULATE (
        SUM ( CUSTOMER_TRANSACTIONS[Amount] ),
        FILTER (
            CUSTOMER_TRANSACTIONS,
            CUSTOMER_TRANSACTIONS[Document Type] == "SR"
                && CUSTOMER_TRANSACTIONS[GroupIndex] > _PreviousIndex
        )
    )
VAR _Sub = _BankBalance - _SR_PAYMENT
RETURN
    _Sub

Result is as below.

107 = 2341.25 - (1850+1500)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.