DAX formula to retreive a value of a column based on a date field in another column

[Kolumam]

How do I retreive a value of a column based on the most recent date of another column ?

[Zubair_Muhammad]

Hi @Kolumam

Do you mean Column or Table?

Please could you paste 5-6 rows of sample data and expected result?

[Kolumam]

So there are two columns here. The measure should display the value of "Percentage Solar over Load" based on the most recen date (i.e. 27/11/2017)

[Zubair_Muhammad]

HI @Kolumam

Please try these MEASURES.

I am assuming Percentage Solar Over Load is a MEASURE

LastDate =
LASTDATE ( ALL ( Table1[Date] ) )

%age on LastDate=
VAR Last_Date =
    LASTDATE ( ALL ( Table1[Date] ) )
RETURN
    CALCULATE ( [Percentage Solar over Load], Table1[Date] = Last_Date )

[Kolumam]

Hi Zubair,

Percentage Solar over load is just a column from excel sheet.

[Kolumam]

Hi Zubair

Instead of retreiving the value based on the most recet date, is it possible to retreive based on yesterday date ? (26/11/2017)

[Zubair_Muhammad]

Hi @Kolumam

Just Wrap LastDate with PreviousDay

Last_Date = PREVIOUSDAY(LastDate(All(Table1[Date])))

[Zubair_Muhammad]

@Kolumam

SecondLastDate =
PreviousDay(LASTDATE ( ALL ( Table1[Date] ) ))

%age on SecondLastDate=
VAR SecondLast_Date =
    PreviousDay(LASTDATE ( ALL ( Table1[Date] ) ))
RETURN
    CALCULATE ( [Percentage Solar over Load], Table1[Date] = SecondLast_Date )

[Kolumam]

Not sure why it is able to find the column ?

[Zubair_Muhammad]

@Kolumam

So its a Column. I thought its a MEASURE

Use Calculate(sum(Meter REadings[Percentage Solar Overload])

calculate cannot have simple columns as arguments

[Kolumam]

It gives me this error now.

[Zubair_Muhammad · Accepted solution]

Hi @Kolumam

replace [Last_Date] in the last line with just secondLast_Date

secondLast_Date should be without brackets because it is the variable in the same formula

[Kolumam]

I realized the mistake yesterday itself. Thanks for your immense help.

[Kolumam]

Hi Zubair,

The formula that you suggested a few months back is not working now. all of a sudden it has gone blank. It says data format is not supported.

Could you please help me with my query ?

http://community.powerbi.com/t5/Desktop/Error-with-DAX-formula/m-p/338849#M151633

Thanks

Krishna