dax subtracting value from another column

Question

I need your help on this below scenarios, We have data in column Date ,A and B.

I need the output like other column "Result"

steps :

A- B = Result

then take value from Result - next row value of B

until the result value get zero.

once you get zero then need to divide the previous value of result and corresponding vale of B . which i am highlight as red color.

this should be in measure. not by calculated column.

Joe Gravelyn Joe Gravelyn · Accepted Answer · 2019-03-14T05:25:56

I will say that your desired result is a little confusing. You first say that you want output like the "Result" column in your screenshot. But then you later say that you want the division of the red numbers. Luckily, to do the division, the "Result" column needs to calculated (at least in a way), so here are both pieces.

First is the "Result" column (which is actually a measure).

The key to that is boiling down the problem to the simplest math terms. The number in the results column for any given row is the value from column A for the max date, minus the sum of column B for that date through the date of the given row (until we hit or go below 0).

First we will need to know what the date is for the given row

    VAR SelectedDate = SELECTEDVALUE(Data[Date])

Then we will need to know the max date.

    VAR MaxDate = MAXX(ALLSELECTED(Data), [Date])

Now that we know the max date, we can get the value from column A for that date.

    VAR MaxDateA = SUMX(FILTER(ALL(Data), [Date] = MaxDate), [A])

With those three pieces, we can create our running total. Take the value of column A for the max date and subtract the sum of column B for all dates between the max date and the selected date (inclusive).

        MaxDateA - SUMX(FILTER(ALL(Data), [Date] >= SelectedDate && [Date] <= MaxDate), [B])

Now we just need to handle the case for when the result hits, or goes below, 0. We can write a simple if statement that looks very similar to the running total above, with one simple change; exclude the selected date. It is basically checking if the prior date's running total is still greater than 0, calculate the running total for the given date.

    IF(
        MaxDateA - SUMX(FILTER(ALL(Data), [Date] > SelectedDate && [Date] <= MaxDate), [B]) > 0,
        MaxDateA - SUMX(FILTER(ALL(Data), [Date] >= SelectedDate && [Date] <= MaxDate), [B])
        BLANK()
    )

Here is the formula in one piece.

Result = 
    VAR SelectedDate = SELECTEDVALUE(Data[Date])
    VAR MaxDate = MAXX(ALLSELECTED(Data), [Date])
    VAR MaxDateA = SUMX(FILTER(ALL(Data), [Date] = MaxDate), [A])

RETURN
    IF(
        MaxDateA - SUMX(FILTER(ALL(Data), [Date] > SelectedDate && [Date] <= MaxDate), [B]) > 0,
        MaxDateA - SUMX(FILTER(ALL(Data), [Date] >= SelectedDate && [Date] <= MaxDate), [B]),
        BLANK()
    )

Next is the division measure.

We'll use some of the same logic as the first part. In this, "Selected" is for the given row context, so it will replace the function of the "Max" in the above.

    VAR SelectedDate = SELECTEDVALUE(Data[Date])
    VAR SelectedA = SUM(Data[A])

Once we have the selected date, we can generate a temporary table that will hold our running total. Basically, we are going to take a distinct list of dates from the data table and add a column (called "Result") that is defined very similarly to the formula for the first part.

    VAR OlderDatesList = SUMMARIZE(FILTER(ALL(Data), [Date] <= SelectedDate), [Date], "Result", SelectedA - SUMX(FILTER(ALL(Data), [Date] <= SelectedDate && [Date] >= EARLIER([Date])), [B]))

Having this list lets us find the threshold when the running total hits (or goes below) 0. From that point, we want to know the date when the threshold was met, the running total right before that date, and the value of column B for that date.

    VAR LastSelectedDate = MAXX(FILTER(OlderDatesList, [Result] <= 0), [Date])
    VAR PriorSelectedResult = SUMX(FILTER(OlderDatesList, [Date] = MINX(FILTER(OlderDatesList, [Date] > LastSelectedDate), [Date])), [Result])
    VAR LastSelectedB = SUMX(FILTER(ALL(Data), [Date] = LastSelectedDate), [B])

We now have both pieces to do the division.

    DIVIDE(PriorSelectedResult, LastSelectedB, BLANK())

Again, here is the formula in one piece.

Division = 
    VAR SelectedDate = SELECTEDVALUE(Data[Date])
    VAR SelectedA = SUM(Data[A])
    VAR OlderDatesList = SUMMARIZE(FILTER(ALL(Data), [Date] <= SelectedDate), [Date], "Result", SelectedA - SUMX(FILTER(ALL(Data), [Date] <= SelectedDate && [Date] >= EARLIER([Date])), [B]))
    VAR LastSelectedDate = MAXX(FILTER(OlderDatesList, [Result] <= 0), [Date])
    VAR PriorSelectedResult = SUMX(FILTER(OlderDatesList, [Date] = MINX(FILTER(OlderDatesList, [Date] > LastSelectedDate), [Date])), [Result])
    VAR LastSelectedB = SUMX(FILTER(ALL(Data), [Date] = LastSelectedDate), [B])

RETURN
    DIVIDE(PriorSelectedResult, LastSelectedB, BLANK())

EDIT: Based on comment, here is the final piece to get the number of rows before the "Result" value hits (or goes below) 0.

The logic for this piece starts the same as the "Division" measure, since we need that OlderDatesList for this calculation as well. Once we have that list, we just need to check if the "Result" column ever hits (or goes below) 0, and if it does, return the number of rows before that point.

Count before Zero = 
    VAR SelectedDate = SELECTEDVALUE(Data[Date])
    VAR SelectedA = SUM(Data[A])
    VAR OlderDatesList = SUMMARIZE(FILTER(ALL(Data), [Date] <= SelectedDate), [Date], "Result", SelectedA - SUMX(FILTER(ALL(Data), [Date] <= SelectedDate && [Date] >= EARLIER([Date])), [B]))

RETURN
    IF(
        COUNTAX(FILTER(OlderDatesList, [Result] <= 0), [Date]) >= 1,
        COUNTAX(FILTER(OlderDatesList, [Result] > 0), [Date]),
        BLANK()
    )

dax subtracting value from another column

1 Answers