CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
pickledmuffin
pickledmuffin

Reputation: 3

How to create a DAX calculated column in PowerBi that finds the MIN value based on another columns FIRSTDATE?

Need some help to calculate the following in two separate columns in a DAX formula

Screenshot of sheet

I've been able to get the first date of each equip with this.

CALCULATE(FIRSTDATE(Transactions[Date]),ALLEXCEPT(Transactions,Transactions[Equip No]))

But cannot work out how to get the 'Reading' value that is associated with the first date

I've managed to do this with a measure, but would also like to get this in a calc. column.

Latest Reading = 
SUMX (
    VALUES( Transactions[Equip No] ),
    CALCULATE ( MIN ( Transactions[Reading] ), FIRSTDATE ( Transactions[Date] ) )
)

Upvotes: 0

Views: 3440

Answers (1)

Agustin Palacios
Agustin Palacios

Reputation: 1204

this should help you.

Earliest Column

Earliest = 
VAR __equipNumber = 'Transaction'[Equip No] //Get the Equip No to filter the main table and create an auxiliar table for every different Equip No.
VAR __minDate = CALCULATE( MIN('Transaction'[Date]), FILTER( 'Transaction', 'Transaction'[Equip No] = __equipNumber ) ) //Get the lowest date asociated to every Equip No.
VAR __subTable = FILTER( 'Transaction', 'Transaction'[Date] = __minDate ) //Create a table that contains 1 row asociate to the lowest date.
Return CALCULATE(SUM('Transaction'[Reading]), __subTable) //Operate over the auxiliar table to get the expected value.

Latest Column

Latest = 
VAR __equipNumber = 'Transaction'[Equip No]
VAR __maxDate = CALCULATE( MAX('Transaction'[Date]), FILTER( 'Transaction', 'Transaction'[Equip No] = __equipNumber ) )
VAR __subTable = FILTER( 'Transaction', 'Transaction'[Date] = __maxDate )
Return CALCULATE(SUM('Transaction'[Reading]), __subTable)

I obtained the expected result

enter image description here

Upvotes: 1

Related Questions