how to segregate de penultime event and date?

[Anonymous]

Hello, I have a table showing weights of catles. Each catle has a ID and some of them are weighted many times in different days. To compare the gain weight I need to segregate the penultime data/day and the last data/day

I treid a table using Table.MIn and Table.Max but the min is not correct. it should be penultime.

pls, I am quite new in PBI. 

thanks in advance

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

diff with last date = 
var _lastdate = 
CALCULATE(
    MAX('Table'[Weighted Date]),
    FILTER(
        ALL('Table'),
        'Table'[Pet ID]=SELECTEDVALUE('Table'[Pet ID])&&
        'Table'[Weighted Date]<SELECTEDVALUE('Table'[Weighted Date])
    )
)
var _lastweight = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Pet ID]=SELECTEDVALUE('Table'[Pet ID])&&
        'Table'[Weighted Date]=_lastdate
    )
)
return
IF(
    NOT(ISBLANK(_lastdate)),
    SELECTEDVALUE('Table'[Value])-_lastweight
)

Result:

Best Regards

Allan

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

[edhans]

Hi @Anonymous ,

I love Power Query, but I think this is best done in DAX using measures. This is my sample data table:

I then created two measures as follows:

Current Weight = 
VAR varCurrentID =
    MAX( 'Table'[ID] )
VAR varCurrentDate =
    CALCULATE(
        MAX( 'Table'[Date] ),
        REMOVEFILTERS( 'Table'[Date] )
    )
VAR varCurrentWeight =
    CALCULATE(
        MAX( 'Table'[Value] ),
        FILTER(
            'Table',
            'Table'[Date] = varCurrentDate
        ),
        REMOVEFILTERS( 'Table'[Date] )
    )
RETURN
    varCurrentWeight

and

Previous Weight = 
VAR varCurrentID =
    MAX( 'Table'[ID] )
VAR varCurrentDate =
    CALCULATE(
        MAX( 'Table'[Date] ),
        REMOVEFILTERS( 'Table'[Date] )
    )
VAR varPreviousweight =
    MAXX(
        CALCULATETABLE(
            FILTER(
                'Table',
                'Table'[Date] < varCurrentDate
            ),
            REMOVEFILTERS( 'Table'[Date] )
        ),
        'Table'[Value]
    )
RETURN
    IF(
        MAX('Table'[Date]) = varCurrentDate,
        varPreviousweight,
        BLANK()
    )

I can then return a table visual like this:

You can then create a third measure if desired subtracting previous from current.

As @camargos88 noted you can do this in a calculated column, and it depends on what you are doing. In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables

See which works best for your scenario. If you really need this in Power Query for some reason, please post back, and provide some sample data for us to work with. We cannot use images without retyping.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Hello Edhans, Thanks so much for your help. I could get the formula. However, I also need to know how many days between the previous date and the last date to know how many kilos per day my catlle gained. should i add this in a table and if so, how? or should it be quick measure?

Sorry, remember that i am still learning this super program.

again,thanks for your help 

[Anonymous]

Using this in power query

let
    
    lastGain = (tab)=>
    let
    pu=Table.MaxN(tab,"Data",2),
    gain=pu[Peso]{0}-pu[Peso]{1},
    int=Duration.Days(pu[Data]{0}-pu[Data]{1})
    in gain/int,
    
    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyNAWSpvrm+kYGRgZKsTpQYQMDsLAZqrChmSWINNI3RogbgUSMQcqNkE0BC4MNx6LcBCRuZKpvChWPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Peso = _t, Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Peso", Int64.Type}, {"Data", type date}},"it-IT"),


    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"lastGain", each lastGain(_)}})
in
    #"Grouped Rows"

with this data:

you get this info:

it is possible to enrich the output with other information:
1) add the number of days of the (last) period;
2) calculate the gain for each period in which the animal was weighed
3) see which type of "pasto" is associated with the maximum / minimum daily gain

[edhans · Accepted solution]

Hi @Anonymous - my measures above have those dates in them. If you want to return the date to a measure, then just return those variables. For example:

Current Weight = 
VAR varCurrentID =
    MAX( 'Table'[ID] )
VAR varCurrentDate =
    CALCULATE(
        MAX( 'Table'[Date] ),
        REMOVEFILTERS( 'Table'[Date] )
    )
VAR varCurrentWeight =
    CALCULATE(
        MAX( 'Table'[Value] ),
        FILTER(
            'Table',
            'Table'[Date] = varCurrentDate
        ),
        REMOVEFILTERS( 'Table'[Date] )
    )
RETURN
    varCurrentWeight

Would just become:

Current Date = 
VAR varCurrentID =
    MAX( 'Table'[ID] )
VAR varCurrentDate =
    CALCULATE(
        MAX( 'Table'[Date] ),
        REMOVEFILTERS( 'Table'[Date] )
    )
RETURN
    varCurrentDate

Current Date is really a misnomer. It is the latest date.

Then, another measure to calculate those differences

Date Difference = DATEDIFF([Current Date],[Previous Date],DAY)

This will return the days between the current date measure and the previous date measure.

Does that help?
You could also replace the dates in these measures if you are calculating the dates already. For example, if Previous Date is:

Previous Date = 
VAR varCurrentID =
    MAX( 'Table'[ID] )
VAR varCurrentDate =
    CALCULATE(
        MAX( 'Table'[Date] ),
        REMOVEFILTERS( 'Table'[Date] )
    )
VAR varPreviousDate =
    MAXX(
        CALCULATETABLE(
            FILTER(
                'Table',
                'Table'[Date] < varCurrentDate
            ),
            REMOVEFILTERS( 'Table'[Date] )
        ),
        'Table'[Date]
    )
RETURN
    IF(
        MAX('Table'[Date]) = varCurrentDate,
        varPreviousDate,
        BLANK()
    )

Then previous weight could become

Previous Weight = 
VAR varCurrentID =
    MAX( 'Table'[ID] )
VAR varPreviousweight =
    MAXX(
        CALCULATETABLE(
            FILTER(
                'Table',
                'Table'[Date] = [Previous Date]
            ),
            REMOVEFILTERS( 'Table'[Date] )
        ),
        'Table'[Value]
    )
RETURN
    IF(
        MAX('Table'[Date]) = [Current Date],
        varPreviousweight,
        BLANK()
    )

And you could clean up Previous Date to use the [Current Date] measure instead of calcualting it inside the Previous Date calc.

[Anonymous]

Dear Edhans,

Sorry for late reply. I am back to this program. It worked! Thanks so much for your help!

🙂

[edhans]

Excellent @Anonymous - glad I was able to assist.

[camargos88]

@Anonymous ,

You can create a custom column to compare the difference between the current and the previous, like:

Column =

VAR _IDL = Table[IDL]

VAR _currentDate = Table[Data PS]

VAR _currentPeso = Table[Peso]

VAR _previousDate = CALCULATE(MAX(TABLE[Data PS]), FILTER(Table), [IDL] = _IDL &&  [Data PS] < _currentDate))

VAR _previousPeso = CALCULATE(SUM(TABLE[Peso]), FILTER(Table,  [IDL] = _IDL && [Data PS] = _previousDate))

RETURN _currentPeso - _previousPeso