Max data point for each month

[garzamalan]

Hi,

I have an issue that I can seem to resolve.

So I have a DB that has the balance (saldo) up to a certain date (Fecha Corrida) (process that runs every sunday) per customer (numcliente)

I want to show a graph (which in these case I converted it to tabel to better show the results), that will provide the max date of Fecha Corrida, and since I have Year / Month / Day hierarchy in the X axis, to calculate the balance of the max date, per each month.

So in the screenshot you can see that I have for the specific numcliente 2573 in october there are 3 dates of Fecha Corrida, I want that table to only show the 10/24/2021, which is the max of that date. 

My measurement is

Cartera Hist = 
CALCULATE(
    SUM(CarteraSnapshots[saldo]),
    FILTER(
        CarteraSnapshots,
        CarteraSnapshots[fecha_corrida] = MAX(CarteraSnapshots[fecha_corrida])))

My data is

numcliente	saldo	Fecha Corrida
2573	17200	10/24/2021
2573	17200	10/17/2021
2573	17200	10/10/2021
2573	17200	9/26/2021
2573	17200	9/19/2021
2573	17200	9/12/2021
2573	17200	9/5/2021

And my dashboard is

Now, when I do not select an specific customer, it seems to work just fine the measurement (balance of the last date for each month), but it messes up when I select a customer.

My expectation is to have the following result

numcliente	saldo	Fecha Corrida
2573	17200	10/24/2021
2573	17200	9/26/2021

[v-yanjiang-msft · Accepted solution]

Hi @garzamalan ,

According to your description, I think there are some loopholes in your formula, the same client and the same month have not been not specified, and MAX function in a measure always return the current value, not the maximum value.

Here's my sample data, I modify the value of saldo column.

My solution is:

Cartera Hist = 
CALCULATE (
    SUM ( CarteraSnapshots[saldo] ),
    'CarteraSnapshots'[fecha_corrida]
        = MAXX (
            FILTER (
                ALLSELECTED ( CarteraSnapshots ),
                'CarteraSnapshots'[Month] = MAX ( 'CarteraSnapshots'[Month] )
                    && 'CarteraSnapshots'[numclient] = MAX ( 'CarteraSnapshots'[numclient] )
            ),
            'CarteraSnapshots'[fecha_corrida]
        )
)

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.