How to calculate SUMIFs in a measure for other measure values using earlier

[muhammadbinzia]

So i have a table in Power BI That is composed of Measures by Credit Note ID, Description, Customer No. , Document Number, Bal and Total. 

Now i need to calculate sumif for these values such as 

Balance = ROUND(SWITCH(TRUE,[Credit Note ID]="NA"&&[Description]<>"Receipt",CALCULATE(SUM([Total]),FILTER(LPS,([Customer No.]=EARLIER([Customer No.]))&&([Document Number]=EARLIER([Document Number]))&&([Bal]=EARLIER([Bal]))))),0)

The problem is that these formulae (SUM and Earlier) only work on columns with row context but not on measures  .....    

If someone can help me with getting the formula that works on measures in the same way for SUMIF...for measures ...an example table is below.

Customer No.	Document Number	Description	Credit Note ID	Bal	Total
100000	8000001	Bill	NA	1	180,000
100000	8000001	Receipt	NA	1	-180,000
100000	8000002	Bill	NA	1	190,000
100000	8000002	Receipt	NA	1	-190,000
100000	8000003	Bill	NA	1	110,000
100000	8000003	Receipt	NA	2	-50,000
100001	8000001	Bill	NA	1	280,000
100001	8000001	Receipt	NA	1	-280,000
100001	8000003	Bill	NA	1	290,000
100001	8000003	Receipt	NA	1	-290,000
100001	8000004	Bill	NA	1	310,000
100001	8000004	Receipt	NA	2	-150,000
100002	8000004	Bill	NA	1	380,000
100002	8000004	Receipt	NA	1	-380,000
100002	8000005	Bill	NA	1	490,000
100002	8000005	Receipt	NA	1	-490,000
100002	8000006	Bill	NA	1	410,000
100002	8000006	Receipt	NA	2	-250,000

[v-jtian-msft]

Hi, Ashish_Mathur ,Bibiano_Geraldo ,MFelix and DataNinja777 ,thanks for your concern about this issue.
And I would like to share some additional solutions below.
Hello,@muhammadbinzia .I am glad to help you.
Like this?

It should be noted that since the calculation logic of power BI dax is based on the whole row from top to bottom, and the order is not reversible, it is not possible to realize that after summing the data in the first row and the data in the second row, the final result is displayed in the first row. (dax requires that the computational logic be executed on each row, from top to bottom.)
Therefore, dax requires that the logic be executed on each row itself.
Like this.

So you need to use an IF judgment to mask the extra calculations
Here's my measure.

If you want to calculate the balance by creating a measure, the result will only be displayed in the row [Description] = “Receipt”, not in the row [Description] = “Bill”.
This is an effect of DAX's calculation logic (from top to bottom, the whole row is calculated, so you can not use DAX to achieve a true iterative cycle of settlement)

You can achieve a similar effect by creating indexes for labeling!
If you find my suggestions helpful, you can check out my tests below
1 Grouping data for labeling by Customer No. (sorting within groups)

Below is my M code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\test_11_27.xlsx"), null, true),
    LPS_Sheet = Source{[Item="LPS",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(LPS_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer No.", Int64.Type}, {"Document Number", Int64.Type}, {"Description", type text}, {"Credit Note ID", type text}, {"Bal", Int64.Type}, {"Total", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer No."}, {{"GroupedData", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)}}),
    #"Expanded GroupedData" = Table.ExpandTableColumn(#"Grouped Rows", "GroupedData", {"Document Number", "Description", "Credit Note ID", "Bal", "Total", "Index"})
in
    #"Expanded GroupedData"

Depending on the size of the Index column to work with the SUM function to achieve an effect similar to iteration (from top to bottom. Compare the index of the current row with the index of the previous row, and add up the Total values of all the indexes smaller than the current row).
At the same time need to ensure that the following filter:

&&'LPS'[Customer No.] = _customerNO &&'LPS'[Document Number] =_doucmentNumber && _creditNoteID = "NA" 

Aggregate functions are commonly used in measure to save the current row of results.
Like this:

Var _index =MAX([index])

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[muhammadbinzia]

Thank you for your help Sir, The Result that is required is like that below:

In this, it would be like the following table [Balance] measure ... Remember all the columns in this table are actually measures so need SUMIFs formula that is based on measures [Customer No.] && [Document Number] && [Bal] only for measures where measure [Credit Note ID] ="NA" && measure [Description] <> "Receipt". So the measure would have result like [Balance] (last column in below table)..The functions SUM(), MAX, SELECTEDVALUE(), EARLIER Only take column as arguments so not working for measures ...Thanks in advance in anticipation of a solution

Customer No.	Document Number	Description	Credit Note ID	Bal	Total	Balance
100000	8000001	Bill	NA	1	180,000	0
100000	8000001	Receipt	NA	1	-180,000
100000	8000002	Bill	NA	1	190,000	0
100000	8000002	Receipt	NA	1	-190,000
100000	8000003	Bill	NA	1	110,000	60,000
100000	8000003	Receipt	NA	2	-50,000
100001	8000001	Bill	NA	1	280,000	0
100001	8000001	Receipt	NA	1	-280,000
100001	8000003	Bill	NA	1	290,000	0
100001	8000003	Receipt	NA	1	-290,000
100001	8000004	Bill	NA	1	310,000	160,000
100001	8000004	Receipt	NA	2	-150,000
100002	8000004	Bill	NA	1	380,000	0
100002	8000004	Receipt	NA	1	-380,000
100002	8000005	Bill	NA	1	490,000	0
100002	8000005	Receipt	NA	1	-490,000
100002	8000006	Bill	NA	1	410,000	160,000
100002	8000006	Receipt	NA	2	-250,000

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result.

[muhammadbinzia]

In this, it would be like the following table [Balance] measure ...Remember all the columns in this table are actually measures so need SUMIFs formula that is based on measures [Customer No.] && [Document Number] && [Bal] only for measures where measure [Credit Note ID] ="NA" && measure [Description] <> "Receipt". So the measure would have result like [Balance] (last column in below table)..The functions SUM(), MAX, SELECTEDVALUE(), EARLIER Only take column as arguments so not working for measures ...Thanks in advance in anticipation of a solution

Customer No.	Document Number	Description	Credit Note ID	Bal	Total	Balance
100000	8000001	Bill	NA	1	180,000	0
100000	8000001	Receipt	NA	1	-180,000
100000	8000002	Bill	NA	1	190,000	0
100000	8000002	Receipt	NA	1	-190,000
100000	8000003	Bill	NA	1	110,000	60,000
100000	8000003	Receipt	NA	2	-50,000
100001	8000001	Bill	NA	1	280,000	0
100001	8000001	Receipt	NA	1	-280,000
100001	8000003	Bill	NA	1	290,000	0
100001	8000003	Receipt	NA	1	-290,000
100001	8000004	Bill	NA	1	310,000	160,000
100001	8000004	Receipt	NA	2	-150,000
100002	8000004	Bill	NA	1	380,000	0
100002	8000004	Receipt	NA	1	-380,000
100002	8000005	Bill	NA	1	490,000	0
100002	8000005	Receipt	NA	1	-490,000
100002	8000006	Bill	NA	1	410,000	160,000
100002	8000006	Receipt	NA	2	-250,000

[Ashish_Mathur]

This measure pattern should work

Measure = calculate([Total],removefilters(Data,Data[Description],Data[Cedit Note ID],Data[Bal]))

Hope this helps.

[muhammadbinzia]

Customer No.	Document Number	Description	Credit Note ID	Bal	Total	Balance
100000	8000001	Bill	NA	1	180,000	0
100000	8000001	Receipt	NA	1	-180,000
100000	8000002	Bill	NA	1	190,000	0
100000	8000002	Receipt	NA	1	-190,000
100000	8000003	Bill	NA	1	110,000	60,000
100000	8000003	Receipt	NA	2	-50,000
100001	8000001	Bill	NA	1	280,000	0
100001	8000001	Receipt	NA	1	-280,000
100001	8000003	Bill	NA	1	290,000	0
100001	8000003	Receipt	NA	1	-290,000
100001	8000004	Bill	NA	1	310,000	160,000
100001	8000004	Receipt	NA	2	-150,000
100002	8000004	Bill	NA	1	380,000	0
100002	8000004	Receipt	NA	1	-380,000
100002	8000005	Bill	NA	1	490,000	0
100002	8000005	Receipt	NA	1	-490,000
100002	8000006	Bill	NA	1	410,000	160,000
100002	8000006	Receipt	NA	2	-250,000

[Bibiano_Geraldo]

Hi @muhammadbinzia ,

Please try the bellow DAX:

Balance Measure = 
VAR CurrentCustomer = SELECTEDVALUE(LPS[Customer No.])
VAR CurrentDocument = SELECTEDVALUE(LPS[Document Number])
VAR CurrentBal = SELECTEDVALUE(LPS[Bal])

RETURN
ROUND(
    SWITCH(
        TRUE(),
        SELECTEDVALUE(LPS[Credit Note ID]) = "NA" &&
        SELECTEDVALUE(LPS[Description]) <> "Receipt",
        SUMX(
            FILTER(
                LPS,
                LPS[Customer No.] = CurrentCustomer &&
                LPS[Document Number] = CurrentDocument &&
                LPS[Bal] = CurrentBal
            ),
            LPS[Total]
        ),
        0
    ),
0)

[muhammadbinzia]

Thanks for the response. However, the problem persists as SelectedValue Function it says doesnt take measures as argument but only columns...while all the upper given are measures and especially measure [Bal] which can only be a measure and not a column...So i dont understand which functions directly work with Measures , Especially in Function Earlier() Context

[MFelix]

Hi @muhammadbinzia ,

Try the following code:

LPS Balance value = 
VAR _tempTable = CALCULATETABLE(
			ALLSELECTED(LPS),
			LPS[Customer No.] = SELECTEDVALUE(LPS[Customer No.]) && LPS[Document Number] = SELECTEDVALUE(LPS[Document Number]) && LPS[Balance] = SELECTEDVALUE(LPS[Balance])
		)
		VAR _CreditNote = CALCULATETABLE(
			_tempTable,
			LPS[Credit Note ID] = "NA" && LPS[Description] <> "Receipt"
		)
		RETURN
			SUMX(
				_CreditNote,
				LPS[Balance]
			)

[muhammadbinzia]

Thank you for you reply.  However, It is saying (A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed)...Also probably The Selected Value Function needs a column instead of measure so it is showing underlined red.  if any other solution can fixt it.

[DataNinja777]

@muhammadbinzia ,

You can replicate the functionality of SUMIF for measures using CALCULATE and FILTER. Here’s an alternative:

Balance Measure = 
ROUND(
    SUMX(
        FILTER(
            LPS, 
            [Credit Note ID] = "NA" &&
            [Description] <> "Receipt" &&
            [Customer No.] = MAX(LPS[Customer No.]) &&
            [Document Number] = MAX(LPS[Document Number]) &&
            [Bal] = MAX(LPS[Bal])
        ),
        LPS[Total]
    ),
    0
)

Best regards,

[muhammadbinzia]

Thanks for the help, however, the MAX function used in it requires a column and doesnt accept measure