last value in time minus penultimate

[Syndicate_Admin]

Good afternoon

I have a table in which I would like to calculate a measure that, filtering by the last record loaded, subtracts me from the last value in time, the penultimate. In the capture you will see it clearer.

My first filter is "Month&Year", which is the last value loaded into system and I round it up in BLUE.

I want to get the result of subtracting 100 and 99.22 (=0.78). Rodeo in RED.

Why? because for my last uploaded value, the last record is in August 2021 and I have to subtract the one from the previous month, July 2021.

The measurement is rare but the calculation must be that.

I've thought several things. As it goes based on a date, take the MAX of that date and subtract the value from it when MAX-30 ("last date minus 30 days"). But of course, not every month has 30 days...

Can you think of something?

[PaulDBrown]

Thanks for providing the sample PBIX file. It is really helpful.

Ok, so this should measure work for you:

Diferencia vs penultimo =
VAR CurrentDate =
    MAX ( Hoja1[Fecha Avance] ) //Returns the date in the filter context
VAR PreviousDate =
    MAXX (
        FILTER (
            ALL ( Hoja1[Fecha Avance] ),
            Hoja1[Fecha Avance] < CurrentDate
                && NOT ( ISBLANK ( [Sum Value] ) )
        ),
        Hoja1[Fecha Avance]
    ) //Returns the last date previous to the current date in which [sum of value] is not blank
RETURN
    [Sum Value]
        - CALCULATE (
            [Sum Value],
            FILTER ( ALL ( Hoja1[Fecha Avance] ), Hoja1[Fecha Avance] = PreviousDate )
        )

And this is the result:

I've attached the file for you

[Syndicate_Admin]

Thank you so much!

It looks very good.

What if I wanted to put the condition in the formula that I do that same calculation with the last date filter in the field "Month & Year"? That would be perfect.

[Syndicate_Admin]

And beware! That I want this for a card. It's not a table. I want this little number.

the specifications should be more, right?

nevertheless. It looks good.

[PaulDBrown · Accepted solution]

For a card visual try:

Difference =

VAR MaxDate = MAX(Hoja1 [Fecha avance])

VAR PrevDate= CALCULATE(MAX(Hoja1[Fecha avance]), FILTER(Hoja1, Hoja1[Fecha avance] < MaxDate))

RETURN

CALCULATE([Sum value], FILTER(Hoja1, Hoja1 [Fecha avance]  = MaxDate)) - CALULATE([ Sum value], FILTER(Hoja1, Hoja1 [Fecha avance] = PrevDate))

[Syndicate_Admin]

Attachment .pbix.

Basically what I want is a measure that I can subtract from the last value in time, the penultimate one. The values I surround. I've tried taking 30 off the maximum value, but obviously it only works for months that have 30 days... And there are 31 and 28 as well.

[Syndicate_Admin]

https://1drv.ms/u/s!AggHtWEAB2tKgUQGw8Pk6z6mGdNA?e=QmHUUp

[PaulDBrown]

Please provide sample data or a PBIX file to work with. Thanks!