measure to filter by the nearest date

[Jean_Ferro]

Hello everyone, I am having trouble finding a solution for this scenario that I will detail:

I need to create a measure that gives me the total sum of the PRODUCT_COST values for each PRODUCT_CODE and FILIAL, filtering based on the closest date that is less than or equal to the date the user selects in the slicer, which in this example is 2024-06-10.

As is:

Date chosen by the user: 2024-06-10

the way I need

[Jean_Ferro · Accepted solution]

I got it with this measure:

VAR FilteredData =

    FILTER(

        'fEstoque',

        'fEstoque'[DTABASE] <= [GreatestDataStock]

    )

VAR SummaryTable =

    SUMMARIZE(

        FilteredData,

        'fEstoque'[SEQPRODUTO],

        'fEstoque'[NROEMPRESA],

        "MaxDtaEntradaSaida", MAX('fEstoque'[DTABASE])

    )

VAR EnhancedTable =

    ADDCOLUMNS(

        SummaryTable,

        "Custo",

        VAR MaxDate = [MaxDtaEntradaSaida]

        RETURN

            CALCULATE(

                MAX('fEstoque'[CSTBRUTO]),

                FILTER(

                    FilteredData,

                    'fEstoque'[DTABASE] = MaxDate &&

                    'fEstoque'[SEQPRODUTO] = EARLIER('fEstoque'[SEQPRODUTO]) &&

                    'fEstoque'[NROEMPRESA] = EARLIER('fEstoque'[NROEMPRESA])

                )

            )

    )

RETURN

    SUMX(EnhancedTable, [Custo])

Although the performance was very slow, but that's for another post, thank you.

[Jean_Ferro]

Hi, i have a update:

With this measure I got exactly what I wanted, but now the problem is that when I change this DATE(2024, 6, 10) to the [GreatestDataStock] measure I get the unexpected exception error, that is, it is only working if I put a fixed date here.


SUMX(
ADDCOLUMNS(
SUMMARIZE(
FILTER(
fStock,
fStock[DTABASE] <= DATE(2024, 6, 10)
),
fStock[PRODUCT_CODE],
fStock[BRANCH],
"MaxDate", MAX(fStock[DTABASE])
),
"Cost",
CALCULATE(
MAX(fStock[COST]),
FILTER(
fStock,
fStock[DTABASE] = [MaxDtaEntradaSaida] &&
fStock[PRODUCT_CODE] = EARLIER(fStock[PRODUCT_CODE]) &&
fStock[BRANCH] = EARLIER(fStock[BRANCH])
)
)
),
[Cost]
)

[Jean_Ferro]

@Anonymous 

Sorry, I don't explained myself very well, I'll try to explain my real objective:

I have a table at the end of the day if a product has moved in stock, my ERP consolidates the quantity of incoming, outgoing and cost of stock for that product in that branch.

I need to produce indicators so that directors can easily check the stock position on a date in the past, that's why I asked to bring the result from the nearest lowest date selected by the user, because if there was no movement on the day the user chose, the first date before the date chosen with movement will be the position the user needs.

I'll show you what I did and what came closest:

I created this measure to capture the largest date chosen by the user in a range:

GreatestDataStock =
MAXX(ALLSELECTED(dCalendar[Date]),[Date])

And then this measurement:

ProductCost =
VAR HighestChosenDate =
CALCULATE (
MAX ( fStock[DTABASE] ),
FILTER (
fStock,
fStock[DTABASE] <= [GreatestDataStock]
)
)
RETURN
CALCULATE (
MAXX (
FILTER (
fStock,
fStock[DTABASE] = HighestChosenDate
),
fStock[PRODCOST]
)
)

When I insert this ProductCost measure into a table, it returns exactly what I need line by line, but the consolidated value does not bring the sum, I will show it with a real image of the PBI.

I need the measure to bring the consolidated sum, I'm having difficulty with this.

[Anonymous]

Hi @Jean_Ferro ,

Don't quite understand why, if filtering by most recent date, when the slicer selects 2024-06-10, 2024-05-02 is filtered out of the results and 2024-03-03 is saved?

The result maybe like below:

NearestDate = 
CALCULATE(
    MAX('Table'[DATE]),
    FILTER(
        'Table',
        'Table'[DATE] <= SELECTEDVALUE('Date'[Date]) &&
        'Table'[PRODUCT_CODE] = MAX('Table'[PRODUCT_CODE]) &&
        'Table'[BRANCH] = MAX('Table'[BRANCH])
    )
)

TotalProductCost = 
CALCULATE(
    SUM('Table'[PRODUCT_ COST]),
    FILTER(
        'Table',
        'Table'[DATE] = [NearestDate] &&
        'Table'[PRODUCT_CODE] = MAX('Table'[PRODUCT_CODE]) &&
        'Table'[BRANCH] = MAX('Table'[BRANCH])
    )
)

Best Regards,
Adamk Kong

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